Excel VLookUP function doesn't always return data

Question

Though I frequently use Excel's VLOOKUP function on various large data sets, I am frequently challenged when it won't return data from a source in which I know the lookup value actually exists. While I am confident that this is somehow a cell formatting issue for with either the lookup value, source data, or even the cell in which the formula is written, I don't know what specific steps I need to follow to correct the problem. I have even tried exporting my data to CSV, then reimporting it to try to "clean up" the data formats.

What is the reason for my varying results with the VLOOKUP function and how can I ensure that it works for me everytime? What cell formats are required for the lookup values, source data, and cell in which the formula actually resides? Should it always work on numeric, text, mixed, or even date formatted fields?

A detailed response would be greatly appreciated, as Murphy's Law seems to strike as the importance of the task increases.

Thank you.

Answer 1

One approach for possibly better results with data as-is (be it text, numeric or mixed data)

you could try using something like this index/match which swings it all to pure text matching

Lookup values in A2 down (to be matched with those in Clients' col A)

In B2,press normal ENTER to confirm:

=INDEX(Clients!$B$2:$B$1000,MATCH(TRUE,INDEX(Clients!$A$2:$A$1000&""=A2&"",),0))

Copy down as far as required

Answer 2

Though I frequently use Excel's VLOOKUP function on various large data sets, I am frequently challenged when it won't return data from a source in which I know the lookup value actually exists. While I am confident that this is somehow a cell formatting issue for with either the lookup value, source data, or even the cell in which the formula is written, I don't know what specific steps I need to follow to correct the problem. I have even tried exporting my data to CSV, then reimporting it to try to "clean up" the data formats.

What is the reason for my varying results with the VLOOKUP function and how can I ensure that it works for me everytime? What cell formats are required for the lookup values, source data, and cell in which the formula actually resides? Should it always work on numeric, text, mixed, or even date formatted fields?

A detailed response would be greatly appreciated, as Murphy's Law seems to strike as the importance of the task increases.

Thank you.

Common problems when doing lookups:

http://contextures.com/xlFunctions02.html#Trouble

--

Biff

Microsoft Excel MVP