1

I have an excel table (Sales Table) that uses an INDEX MATCH function within a few columns. There are 2 specific columns (Product and Part Number) which are using a material number column to pull other values from other tables. The material number column is filled with numbers that are stored as "General" and each cell has an error that says "Number Stored as Text". The "Number stored as text" is not a problem for the product column but IS a problem for the part number column. When I change the column to "Number" the part number column fixes itself (so I know its not a problem with the function).

Additionally, the part number reference table, has the material numbers and DOES NOT show an error on the material number values. However, in the product table, the material number DOES show an error.

Can anyone explain why the numbers stored as text would affect one column and not the other? FYI: The data source is imported into the main table through VBA and I cannot change the input file contents. Maybe I need to change the column text through VBA on import but I really want to understand why its a problem in one spot but not the other.

Formula for Product:

=IF([@Location]="Non-AT","Non AT",INDEX(Table_Material,MATCH([@[Material Number]],Table_Material[Material Number],0),MATCH($T$2,'Material No. to Product'!Print_Titles,0)))

Formula for Part Number:

=INDEX(Table_ATNo,MATCH([@[Material Number]],Table_ATNo[SAP Material Number],0),2)

Main Table

Ref Table

enter image description here

Improve this question
1
  • In order to coerce numbers/general inputs to strings you can also just append a null string to the search parameters; so something like: MATCH([@[Material Number]]&"",Table_Material[Material Number]&"",0) Commented Mar 4 at 3:33

1 Answer 1

Reset to default
3

It seems that the issue is related to how different functions in Excel handle number-to-text conversions. Some functions perform implicit conversions, while others do not. For example, in the picture below, "text" 1 (cell A1 contains '1) minus "numeric" 1 equals zero, but comparing the same values returns FALSE.

enter image description here

You are essentially creating your own problem by comparing two different data types. To resolve this, you should wrap the "general" values in a function that returns their numeric values. This should sort out your problem. For example:

=INDEX(Table_ATNo,MATCH(VALUE([@[Material Number]]),Table_ATNo[SAP Material Number],0),2)

Edit: It seems that INDEX does perform the implicit conversion, but MATCH does not. This actually makes sense, as INDEX expects numerical inputs for the column and row numbers, but the first parameter of MATCH does not have to be numeric.

This highlights the importance of using consistent data types in your formulas.

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.