excel – Cant find the error: VLOOKUP not returning the value

excel – Cant find the error: VLOOKUP not returning the value

Using this formula will address the issue highlighted by RocketDonkey

=VLOOKUP(TEXT(D1,#),$A$1:$B$219,2,FALSE)

The issue is that you havent converted you values in column A to numbers. Since the first three values in your lookup range are themselves strings, they are matching the stringified numbers.

In order to fix, click on one of the numbers in column A, press Ctrl+Space to select the entire column, and then click on the exclamation mark and select Convert to number:

enter

After that, youll notice that your previous values now show errors, so follow the same process to conver them to numbers as well. @nutschs solution is does not require you to modify your data, so if the type of your data is not crucial, that is a good fix as well 🙂

enter

excel – Cant find the error: VLOOKUP not returning the value

This has been a major pain for me as well(Excel is full of stupid assumptions). What I do is copy the entire column to a text editor, change the column to match the lookup column, and copy and paste the data back into the column. This has worked well for me.

Leave a Reply

Your email address will not be published.