Vlookup reporting #N/A's

[D&E Communications]

What's the deal....I have legitimate data in both the lookup column as well
as the return column, both formatted as numbers, as well as my formula cell,
but upon looking up it is finding nothing. Can someone give me some common
errors to check for as far as using the VLOOKUP function?

TIA,
Greg

[Duke Carey]

It's not uncommon for a cell's value to *look* like a number but be text, so
Excel will not match it when comparing it to a real, true number. This is
espcially common when one of the numbers, or your lookup table, for instance,
comes from an external source.

You can try

=VLOOKUP(VALUE(lookup value),table,...) - which will fix it if your lookup
value is actually text.

If that doesn't work, copy a blank cell, select the first column of your
lookup table, and Edit>Paste Special>Add. That will force the lookup table
keys to be numeric.

Good luck


"D&E Communications" wrote:

> What's the deal....I have legitimate data in both the lookup column as well
> as the return column, both formatted as numbers, as well as my formula cell,
> but upon looking up it is finding nothing. Can someone give me some common
> errors to check for as far as using the VLOOKUP function?
>
> TIA,
> Greg
>
>
>

[Alan]

Further to Dukes advice, check to see if there are any leading or trailing
spaces in the table, especially if the data has been imported. The TRIM
function will remove them or simply Edit > Replace 'Space' with nothing.
If the data came from a web site, they have habit of using the ASCII
character 160 which looks like a space, (ASCII32), but isn't. Use =CODE(your
cell) to check, if that returns 160 then you have them.
To remove these if they exist, highlight the whole table, use Edit >
Replace. Replace What, hold down the Alt key and type 0160 on the numeric
keypad, (nothing will appear in the box), Replace With, Nothing, Replace
All,
Regards,
Alan.
"Duke Carey" <[email protected]> wrote in message
news:[email protected]...
> It's not uncommon for a cell's value to *look* like a number but be text,
> so
> Excel will not match it when comparing it to a real, true number. This is
> espcially common when one of the numbers, or your lookup table, for
> instance,
> comes from an external source.
>
> You can try
>
> =VLOOKUP(VALUE(lookup value),table,...) - which will fix it if your
> lookup
> value is actually text.
>
> If that doesn't work, copy a blank cell, select the first column of your
> lookup table, and Edit>Paste Special>Add. That will force the lookup
> table
> keys to be numeric.
>
> Good luck
>
>
> "D&E Communications" wrote:
>
>> What's the deal....I have legitimate data in both the lookup column as
>> well
>> as the return column, both formatted as numbers, as well as my formula
>> cell,
>> but upon looking up it is finding nothing. Can someone give me some
>> common
>> errors to check for as far as using the VLOOKUP function?
>>
>> TIA,
>> Greg
>>
>>
>>

[spog00]

If you are looking for an exact match, check you have set the 'Range Lookup' to FALSE.

Alastair