Home

Use numerical lookup with VLOOKUP?

SeveralDatum0101
New Contributor

I would like to populate a sample information array based on a numerical sample ID.  However, when I use a VLOOKUP function with a numerical lookup (see screenshot), I get a bizarre result where initial values return "#N/A" and the last few cells return the desired result.  This problem is fixed when I use a text value for the sample ID.  Can VLOOKUP be used with a numerical LOOKUP value?  What could be causing the strange error I'm observing when using a numerical lookup value?

 

Edit: Altering the formats does not fix the issue.  The same issue happens regardless of whether columns are formatted as general, numbers, or text.

 

UPDATE: Ok, so the sample ID cell that I was attempting to use as lookup value is part of a long chain of linked cells spanning several worksheets.  I did this because there are several steps to the data analysis process, and I'm looking to automate results generation by linking raw data to output.  Anyway, the first sample ID "1", the first link in the chain - the cell had a warning indicating that the "number is stored as text".  This may have happened because I applied a sort to sample IDs, some of which are are numbers and some of which are text.  Either way, by simply converting the original cell, I was able to fix the problem.  So the lesson is make sure your formats are consistent across the board. 

2 Replies

Weird. Yes you can use VLOOKUP for numerical values. I am assuming everything both sets of values are formatted as numbers. I was able to recreate this issue by changing between text and number formatting, same as you it only effects the last few numbers. Changing formatting does not fix it, oddly double clicking into 11,12 and 13 (In the table array) and hitting enter fixes it for me. Very strange. 

See my edited post.