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.

Add the false parameter to your formula. That will return exact matches instead of the first partial match it reaches — Rob White, May 23 '17 at 12:13
Does `=VLOOKUP(A3, Asset_Mapping!A$2:B$673, 2, FALSE)` work? — , May 23 '17 at 12:14

Rob White · Accepted Answer · 2017-05-23T12:27:17.580

9

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.

edited May 23 '17 at 12:27

answered May 23 '17 at 12:15

Rob White

950
1
6
16

1

I've found it useful to mention that partial (*aka* approximate or binary search) matches can only return reliable results on sorted data. – May 23 '17 at 12:23
THANK YOU! I hadn't done this in forever and forgot about the FALSE parameter! Thank you! – Alexx May 23 '17 at 12:34

score 6 · Answer 2 · answered Mar 22 '19 at 23:20

6

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

answered Mar 22 '19 at 23:20

Steven Robert Richmond

61
1
1

I've had this issue reoccurring sporadically with office for Mac, thank you! – alexwatever Dec 10 '19 at 01:43

score 3 · Answer 3 · answered May 23 '17 at 12:16

3

Also lookup value must be in first column.

answered May 23 '17 at 12:16

prasenjit barman

31
2

score 0 · Answer 4 · answered May 23 '17 at 12:18

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.

score 0 · Answer 5 · answered Feb 12 '19 at 21:04

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.

score 0 · Answer 6 · edited Dec 15 '20 at 07:37

0

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

edited Dec 15 '20 at 07:37

sbgib

5,580
3
19
26

answered Dec 14 '20 at 16:49

Dakota Massey

1

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 – Dakota Massey Dec 14 '20 at 17:20
2

Please add that into your answer... And make it look like an answer (remove the question part) – Sabito stands with Ukraine Dec 14 '20 at 17:26

Excel VLOOKUP returning same value in all rows

6 Answers6