VLookup Pulling Data from one row above
 

Hello-I am trying to use vlookup to return age, based on name. Here's what I
have:
A B
1 Jones 35
2 Smith 42
3 Charles 50

I use Vlookup (a1(from other sheet), $a$1:$bB3, 2). I can't use False
because the names are slightly different in sheets 1 and 2 (some have middle
names, nicknames, etc). The problem is that it is returning the data from one
row above instead of the correct row. i.e. if I vlookup Smith, I get the age
35.

What am I doing wrong? Thanks!

Answer: VLookup Pulling Data from one row above
 

Hi there! It sounds like you're having trouble with your VLOOKUP formula returning data from one row above instead of the correct row. Don't worry, this is a common issue that can be easily fixed.

The reason why your formula is returning data from one row above is because you're not specifying the exact range of cells that you want to search in. When you use $a$1:$bB3 as your lookup range, you're telling Excel to search in all cells from A1 to B3, which includes the cell above the correct result.

To fix this, you can modify your VLOOKUP formula to include an additional argument that tells Excel to only search in column A for the name you're looking for. Here's the modified formula:

1. =VLOOKUP(A1, A:B, 2, FALSE)

In this formula, A1 is the name you're looking for, A:B is the range of cells that you want to search in (only column A will be used for the lookup), 2 is the column number that contains the age data, and FALSE ensures that an exact match is found.

Give this formula a try and let me know if it works for you. If you have any other questions or concerns, feel free to ask!

VLookup Pulling Data from one row above
 

In order for vlookup to function properly, your look table (A1:B3) needs to
be sorted by column A) which it is not in the sample you provided.

"ELeigh" wrote:

Hello-I am trying to use vlookup to return age, based on name. Here's what I
have:
A B
1 Jones 35
2 Smith 42
3 Charles 50

I use Vlookup (a1(from other sheet), $a$1:$bB3, 2). I can't use False
because the names are slightly different in sheets 1 and 2 (some have middle
names, nicknames, etc). The problem is that it is returning the data from one
row above instead of the correct row. i.e. if I vlookup Smith, I get the age
35.

What am I doing wrong? Thanks!

VLookup Pulling Data from one row above
 

=VLOOKUP(A1,$A$1:$B$3,2)
Anchor the lookup range with the dollar signs - otherwise the lookup range
moves as the formula moves

"ELeigh" wrote:

Hello-I am trying to use vlookup to return age, based on name. Here's what I
have:
A B
1 Jones 35
2 Smith 42
3 Charles 50

I use Vlookup (a1(from other sheet), $a$1:$bB3, 2). I can't use False
because the names are slightly different in sheets 1 and 2 (some have middle
names, nicknames, etc). The problem is that it is returning the data from one
row above instead of the correct row. i.e. if I vlookup Smith, I get the age
35.

What am I doing wrong? Thanks!

VLookup Pulling Data from one row above
 

Hi guys-I have done both of those things, sorted by alpha ascending and
anchoring with dollar signs, and it still pulls incorrectly. Thanks for the
suggestions though.

"dhstein" wrote:

=VLOOKUP(A1,$A$1:$B$3,2)
Anchor the lookup range with the dollar signs - otherwise the lookup range
moves as the formula moves

"ELeigh" wrote:

Hello-I am trying to use vlookup to return age, based on name. Here's what I
have:
A B
1 Jones 35
2 Smith 42
3 Charles 50

I use Vlookup (a1(from other sheet), $a$1:$bB3, 2). I can't use False
because the names are slightly different in sheets 1 and 2 (some have middle
names, nicknames, etc). The problem is that it is returning the data from one
row above instead of the correct row. i.e. if I vlookup Smith, I get the age
35.

What am I doing wrong? Thanks!

VLookup Pulling Data from one row above
 

As an example, what text is in A1 on the other sheet?

"ELeigh" wrote:

Hello-I am trying to use vlookup to return age, based on name. Here's what I
have:
A B
1 Jones 35
2 Smith 42
3 Charles 50

I use Vlookup (a1(from other sheet), $a$1:$bB3, 2). I can't use False
because the names are slightly different in sheets 1 and 2 (some have middle
names, nicknames, etc). The problem is that it is returning the data from one
row above instead of the correct row. i.e. if I vlookup Smith, I get the age
35.

What am I doing wrong? Thanks!

Use =(TRIM(CLEAN(A1))) to clean up your data first. I had the same problem and this worked: Insert a column next to your search data (in your case column A). In the new column duplicate your data by typing =(TRIM(CLEAN(A1))) in the first row, then copy/paste down. This will give you a cleaned-up version of your data. Do the same for the other sheet, but your cleaned-up data should be in the first column of your range. Make sure your vlookup formula points to the cleaned-up data.

When I did this, my vlookup pulled from the correct row. It fixed my problem!