VLOOKUP function error when looking up a column containing a mix of numerical and alphanumerical entries

Question

Hi,

I'm using the VLOOKUP function where the lookup values are a mix of numerical and alphanumerical entries.

The function works and returns results for the rows with an alphanumerical entry but return an #N/A error on rows containing a purely numerical entry.

I've checked for spaces etc and tried changing the cell type of the numerical cells to Number in both sheets but I'm still receiving the error.

Any help or ideas would be much appreciated.

Thanks,

Tom

Answer 1

Hi Tom RXZ,

Welcome to post back if you still need help on the formula.

Regards,

Clark

Answer 2

I've checked for spaces etc and tried changing the cell type of the numerical cells to Number in both sheets

First, you neglect to show us the actual formula.

If you have mixed data types, you probably should use FALSE or zero for the 4th parameter (exact match).

I have never tested VLOOKUP with mixed data types and TRUE in the 4th parameter (approximate match).  For reliable results, I suspect that we should put all text at the end of the lookup table, sorted according to Excel sort order, which is not exactly ASCII order.

That would conform with the general requirement for approximate VLOOKUP(...,TRUE), to wit:  the data must be in ascending order.

Second, changing the format of a cell ("to Number") does not change the type of the cell value, unless you "re-enter" the data afterwards.

Moreover, a numeric cell format does not change the type of text that Excel cannot interpret as numeric (which includes dates, currency, percentages etc).

Finally, the type of the lookup value (1st parameter) must be the same as the type of the data to be matched in the lookup table.

Answer 3

Hi Tom RXZ,

I go through the post. The issue could be related to the formula you are using. Could you please share the formula here with us? Besides, it is better to share a screenshot of sample data here with us so that we can try modifying the formula for you based on the data.

Best Regards,

Clark