Mixed Data Type using Index Match

[Shetara]

Hello,

I am trying to pull from a column within a table that has mixed data types (numbers & Text comine) and place it into another table. When I am doing so, some items return as the exact match and others read a "N/A". I am looking for an excel formula to return/match all items needed.

Here is sample data:
(Where I would like the data to go) - Item Call
[TABLE="width: 500"]
<tbody>[TR]
[TD]Ref Number[/TD]
[TD]Item Call[/TD]
[/TR]
[TR]
[TD]912364[/TD]
[TD]Purse[/TD]
[/TR]
[TR]
[TD]18666B6[/TD]
[TD]N/A[/TD]
[/TR]
</tbody>[/TABLE]

Data Source:
[TABLE="width: 500"]
<tbody>[TR]
[TD]Ref Number (Match)[/TD]
[TD]Item Call (Index)[/TD]
[/TR]
[TR]
[TD]912364[/TD]
[TD]Purse[/TD]
[/TR]
[TR]
[TD]18666B4[/TD]
[TD]Shoes[/TD]
[/TR]
</tbody>[/TABLE]

 

[jtakw]

Hi,

Your description is not clear. Are you saying you want 18666B6 to be a match for 18666B4?, or are you saying you don't want the #NA error to show?

 

[krogh101]

Hi,

I guess the easiest solution would be to just use a helper-column in the data source to convert all values to text, and then use the TEXT function to convert the lookup value to text.

Another solution is to use the IFERROR function, and use the same index match twice, but forcing the lookup value to numbers for the first, and text for the second if the first results in an error.