Excel VLOOKUP returning same value in all rows

user_253318 0
Viewed 80020

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.

vlookup excel-formula excel
6 Answers

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".

user_112453540

Just in case adding a solution to this topic. Don't know why in big tables Vlookup and Index+Match tend to show just the value of the first result in all the other cells.

In my case to fix this, after I've copied the formula in the whole range, I choose any cell containing it, press F2 and then Enter. It kind of forces excel to process everything.

user_109851050

I've been working on this for a week with no results. I've created this same spreadsheet for another apartment community without any issues, but for some reason, I cannot get the proper results in this spreadsheet. I've tried vlookup as well, but that didn't help either. any more advice?

Formula

Formula

Result and lookup vectors

Result and lookup vectors

user_148248160
Related