Vlookup Not Working

[Groovy Chick]

Hi, I have two tabs on a spreadsheet called LOPC and Tiers. From the LOPC file I have a formula that looks at the value in cell A2 and so on to find a match in column A on the Tiers tab and return the text in column C at the Tiers tab if it finds a match or no match if it doesn't. I am using the formula

=IFERROR(VLOOKUP(A2,Tiers!A2:C443,3,FALSE),"No Match")

This works insomuch as it returns No Match but it is returning No Match even when there is a match. Can someone advise me what I am doing wrong? Thank you.

 

[Joe4]

What do the values in column A on each sheet look like?
Also, if you are copying this formula down the column, you will need to lock your lookup range like this (before copying the formula down):

=IFERROR(VLOOKUP(A2,Tiers!A$2:C$443,3,FALSE),"No Match")

 

[Groovy Chick]

What do the values in column A on each sheet look like?
Also, if you are copying this formula down the column, you will need to lock your lookup range like this (before copying the formula down):

=IFERROR(VLOOKUP(A2,Tiers!A$2:C$443,3,FALSE),"No Match")

Hi, thank you. I tried that but no success, sadly. Both values are formatted as numbers.

 

[Joe4]

Please post some samples of what both numbers look like.

Are both right-justified in the cells, like this?

Or are any left-justified, like this?

 

[Groovy Chick]

Please post some samples of what both numbers look like.

Are both right-justified in the cells, like this?
View attachment 99382

Or are any left-justified, like this?
View attachment 99383

Hi, both look like this:

 

[Alex Blakenburg]

Ideally you want to clean up your data. From the images the 1st is more than likely text while the second one is most likely number.
You don't give the sheet names so we don't know which image is where the A2 is reading from.

Depending on which sheet is which one of these first 2 might work, the last one covers off both cases.

Convert A2 to a number

=IFERROR(VLOOKUP(VALUE(A2),Tiers!A$2:C$443,3,FALSE),"No Match")

or
Convert A2 to text

=IFERROR(VLOOKUP(TRIM(A2),Tiers!A$2:C$443,3,FALSE),"No Match")

or

[CODE=xls]=IFERROR(VLOOKUP(TRIM(A2),Tiers!A$2:C$443,3,FALSE),IFERROR(VLOOKUP(VALUE(A2),Tiers!A$2:C$443,3,FALSE),"No Match"))

 

[Joe4]

Hi, both look like this:

View attachment 99430

View attachment 99431

The fact that one is left-justified suggests it is a text entry, and the one that is right-justified is a numeric entry.
Whenever using any of the lookup or match functions in Excel, the two data types you are comparing MUST be the same data type!
You cannot match text entries to numeric ones. Either they both need to be numeric, or they both need to be text.

You can use "Text to Columns" on your "text" entries to quickly convert that whole column to numbers.
Then your formulas should work as desired.

 

[Groovy Chick]

Ideally you want to clean up your data. From the images the 1st is more than likely text while the second one is most likely number.
You don't give the sheet names so we don't know which image is where the A2 is reading from.

Depending on which sheet is which one of these first 2 might work, the last one covers off both cases.

Convert A2 to a number

=IFERROR(VLOOKUP(VALUE(A2),Tiers!A$2:C$443,3,FALSE),"No Match")

or
Convert A2 to text

=IFERROR(VLOOKUP(TRIM(A2),Tiers!A$2:C$443,3,FALSE),"No Match")

or

[CODE=xls]=IFERROR(VLOOKUP(TRIM(A2),Tiers!A$2:C$443,3,FALSE),IFERROR(VLOOKUP(VALUE(A2),Tiers!A$2:C$443,3,FALSE),"No Match"))

Thank you, Alex. I had converted both to numbers and aware that data types need to be the same. All working now.

 

[Groovy Chick]

The fact that one is left-justified suggests it is a text entry, and the one that is right-justified is a numeric entry.
Whenever using any of the lookup or match functions in Excel, the two data types you are comparing MUST be the same data type!
You cannot match text entries to numeric ones. Either they both need to be numeric, or they both need to be text.

You can use "Text to Columns" on your "text" entries to quickly convert that whole column to numbers.
Then your formulas should work as desired.

Thank you, Joe. I was aware that the data types had to be the same and thought I had converted both to numbers, my fault. All working now, thank you for your help.

 

[Joe4]

You are welcome.
Glad we were able to help.