Excel VLOOKUP returning same value in all rows

Question

The vlookup function appears to be broken. It is returning the same value for all lookups, but it should not.

I have it set like so:

=VLOOKUP(A3, Asset_Mapping!A$2:B$673, 1)

Where the lookup value is in the B column and the result is in the A column.

I use the $ so the rows are not auto-updated to A3:B674 etc as I paste the formula down the rows. However, even if I manually enter that formula into the next row, it is not finding the correct value.

IF i remove the "$", the correct values are found for the first rows where the values fall within the modified range (e.g. Asset_Mapping!A3:B674) but eventually as expected it stops finding the values as the range is invalid.

What am I doing incorrectly here? I have formulas set to auto-calculate.

Answer 1

Without testing on your actual data it's hard to confirm this will work but add the false parameter. This will find exact matches and not the first partial match.

=VLOOKUP(A3, Asset_Mapping!A$2:B$673, 1, false)

Collating the points together and clarifying the formula

Parameter 1: The value you are looking for

Parameter 2: The table with the data. First column is the value you are looking for.

Parameter 3: The column number of the value you want to show.

Parameter 4: If you want an exact match or partial match.

@Jeeped made the point of ordering data to get more reliable results. Good advice.

Answer 2

Andres has the right idea, but there is a faster way to fix the problem.

If it is returning the same value over and over again for your whole range, you probably have your Calculation Options set to "Manual".

Go into Formulas on the top ribbon and choose Calculation Options. There, you can change your calc method to "Automatic".

Answer 3

Also lookup value must be in first column.