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.

Broomstick

Add the false parameter to your formula. That will return exact matches instead of the first partial match it reaches – Monica

Does =VLOOKUP(A3, Asset_Mapping!A$2:B$673, 2, FALSE) work? – Cataract

Yep, it was the FALSE parameter. Thanks everyone! – Broomstick

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.

Monica

I've found it useful to mention that partial (aka approximate or binary search) matches can only return reliable results on sorted data. – Cataract

THANK YOU! I hadn't done this in forever and forgot about the FALSE parameter! Thank you! – Broomstick

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

Sweepings

I've had this issue reoccurring sporadically with office for Mac, thank you! – Exercitation

Answer 3

Also lookup value must be in first column.

Mayfield

Answer 4

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

VLOOKUP always lookup in the left-most column and returns a value from the column number specified. If you want to reverse the lookup/return order then use INDEX(..., MATCH(...))

=INDEX(Asset_Mapping!A$2:A$673, MATCH(A3, Asset_Mapping!B$2:B$673, 0))

Note that you were using a sorted, binary lookup; I've changed this to an exact match lookup which does not require sorted data.

Cataract

Answer 5

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.

Banger

Answer 6

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

Result and lookup vectors

Bremen

omg, i figured it out. So if anyone else has this issue, the issue was that i copied and pasted the data, so the lookup vector data was all formatted incorrectly, so i had to click on each cell in the lookup vector and then press enter. This reformatted the data so that it could be properly looked up. this literally took me 3 days to figure out. lol – Bremen

Please add that into your answer... And make it look like an answer (remove the question part) – Psychoneurotic