VLOOKUP Function

[infinite1013]

I have 2 worksheets. I am trying to use a number(L2) on sheet 2 to reference
column A on sheet 1 and return a percentage from column 5 on the sheet 1. In
order to avoid the #N/A result, I am using the following formula, but getting
0.00% returned when I know there should be another result. This formula has
worked for me before, but can't figure out why it isn't working this time.
Please help! The formula looks like this:

=IF(ISERROR(VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE)),0,VLOOKUP(L2,'CHARGES'!A$1:A$250,5,FALSE))

 

[Mike H]

Hi,

You have a single column array in your vlookup 'CHARGES'!A$1:A$250
and are trying to return something from column 5. Should it be this
'CHARGES'!A$1:E$250


Mike

 

[infinite1013]

Sorry, that was a typo. I do have it set up as 'CHARGES'!A$1:E$250. I have
used this exact formula on the identically formatted worksheets for 3 months.
I just can't see where I am going wrong. Thanks.

 

[infinite1013]

I'm not sure if this helps, but when i open the argument box, it shows that
the VALUE IF FALSE= #N/A in red.

 

[Mike H]

Hi,

With the range correct theres nothing wrong with the formula so if it's
returning 0 then it isn't finding a match for L2 in the lookup array. Do a
manual check. Copy your value from L2 andpaste it into a cell in 'Charges'.
Rember Copy and paste don't type it.

manually find a match in column A an enter this formula in a cell =A10=H10
If they really match it will return TRUE, I suspect it will return False and
the likely culprits are spaces.

Mike

 

[infinite1013]

Okay. That helps. The result of this was FALSE. The spacing appears to be the
same, but not sure. Any way to check this?

 

[Mike H]

Hi,

Len(a1) tells you how many characters are in the cell which you can compare
to how many you think you have. The rogue spaces are 'generally' leading or
traling and can be removed using

=Trim(a1) and drag down
You can then copy this corrected range and
Paste special - values on top of the offending range in column A.

Mike

 

[infinite1013]

Success! Thanks so much!

Hi,

Len(a1) tells you how many characters are in the cell which you can compare
to how many you think you have. The rogue spaces are 'generally' leading or
traling and can be removed using

=Trim(a1) and drag down
You can then copy this corrected range and
Paste special - values on top of the offending range in column A.

Mike

 

[Mike H]

Your most welcome, glad I could help

Success! Thanks so much!