Vlookup returns #N/A when Range is 'false', but returns values when 'true'

[natalie]

Hello,

I'm Natalie.

Im trying to merge data from a few different worksheets and am using a basi
vlookup formula to do so. When I type in the formula using a 'false' rang
lookup, I get an #N/A. When I type 'true', it returns the value. The proble
is that sometimes it returns the incorrect value...therefore, true isn't goin
to work.

Can I get some help? Here is the formula:

=VLOOKUP(A2, 'IMF POP'!A$2:B$183,2, FALSE)

Thank you!

Natalie

 

[Pete_UK]

If you use FALSE for the 4th parameter you will be looking for an
exact match, which is presumably what you want. However, if it is not
there it will return #N/A. It may look to you like there is an exact
match, but you might have a situation where you have numbers for your
lookup_value but you have text values in your lookup table (or vice
versa). Or you might have numbers with decimals which are formatted
not to show the full value. If you are trying to match with strings,
then there might be extra spaces in lookup_value or in your table
which means there will not be an exact match.

You can avoid returning #N/A by changing your formula to this:

=IF(ISNA(VLOOKUP(A2, 'IMF POP'!A$2:B$183,2, FALSE)),"not
present",VLOOKUP(A2, 'IMF POP'!A$2:B$183,2, FALSE))

or some other message.

If you use TRUE for the 4th parameter the function will match with the
highest value which is less than or equal to the lookup_value. The
data in the lookup table has to be sorted on the first column for this
to work, and it might still not find the correct value.

You need to check out your data.

Hope this helps.

Pete

 

[natalie]

natalie wrote on 03/05/2010 14:21 ET :

Hello,

I'm Natalie.

Im trying to merge data from a few different worksheets and am using a basic
vlookup formula to do so. When I type in the formula using a 'false' range
lookup, I get an #N/A. When I type 'true', it returns the value. Th problem
is that sometimes it returns the incorrect value...therefore, true isn' going
to work.

Can I get some help? Here is the formula:

=VLOOKUP(A2, 'IMF POP'!A$2:B$183,2, FALSE)

Thank you!

Natalie

I found the problem. I pulled all of the different worksheets in from differen
websites. So I think the problem is in the formatting. *sigh* Still nee
help...

 

[natalie]

Pete_UK wrote on 03/05/2010 17:29 ET :

If you use FALSE for the 4th parameter you will be looking for an
exact match, which is presumably what you want. However, if it is not
there it will return #N/A. It may look to you like there is an exact
match, but you might have a situation where you have numbers for your
lookup_value but you have text values in your lookup table (or vice
versa). Or you might have numbers with decimals which are formatted
not to show the full value. If you are trying to match with strings,
then there might be extra spaces in lookup_value or in your table
which means there will not be an exact match.

You can avoid returning #N/A by changing your formula to this:

=IF(ISNA(VLOOKUP(A2, 'IMF POP'!A$2:B$183,2, FALSE)),"not
present",VLOOKUP(A2, 'IMF POP'!A$2:B$183,2, FALSE))

or some other message.

If you use TRUE for the 4th parameter the function will match with the
highest value which is less than or equal to the lookup_value. The
data in the lookup table has to be sorted on the first column for this
to work, and it might still not find the correct value.

You need to check out your data.

Hope this helps.

Pete

Hi Pete,

I downloaded the data from various websites. So it looks like the problem i
in the formatting. I can do the vlookup with the false value on the worksheet
that have the same data from the same website on them... but I can't do i
between worksheets from different websites.

Is there a way to format my data across worksheets so that it's all standard?
So, for instance, taking out all of the extra spaces, etc...

Thanks!

 

[Pete_UK]

If you have downloaded data from websites then you are likely to have
a lot of non-breaking space characters (code 160) in there. You can
get rid of them by highlighting the columns affected and using
Find&Replace (CTRL-H):

Find what: Alt-0160
Replace with: leave blank
Click Replace All

where Alt-0160 means to hold down the Alt key while typing 0160 on the
numeric keypad.

If you have double-spaces in there you can do the same, by putting 2
spaces in the Find box and a single space in the Replace box.

If this is a recurrent requirement, you can record a macro while you
do this once, and then just re-run the macro after you have imported
some more data.

Hope this helps.

Pete