EXCEL VLOOKUP RETURNING #N/A 1

[kgreer]

I have two excel spread sheets. One Spread sheet is used in my vlookup command. When I run the command it returns the value of #N/A on some of the outputs. The value I am looking up is in the lookup table. Below is the some of the information in the spreadsheets:

Look Up Table:
Item Class BU
11 3111
22 3111
62 3111
91 3111
92 3111
93 3111
94 3111
95 3111
96 3111
97 3111


Main table:
ITCLS
ATA
DGE
DTA
GTA
11
22
31A
32F0
3375
3379
52F5
611
612
7371
871
872
91
92
93
94
95
9999


All the values in the Main table are in the other table. What I want to return in the main table is the BU Number. Instead it returns #N/A for all the values above. Here is the Vlookup:
=VLOOKUP(TRIM(B2),'[Revised BU Product Lookup Table 2004.xls]New Report'!$B:$C,2,FALSE)


Any help would be appreciated.

 

[xlbo]

Classic example - bet the one's that fail are all completely numeric !!
TRIM forces a number to text so it will not match a true numeric value in your lookup table

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[kgreer]

Even if I take the trim out it still gives me a #N/A return.

 

[xlbo]

in which case apply the =ISTEXT() or =ISNUMER() formulae to both the cell that is being used as the lookup and the matching reference in the lookup table.

That should tell you if it is a data type issue


Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[Bob44TheCat]

Hi,
Another possibility is that you have hidden characters in the cells you're looking up (either in the from or to worksheet). Excel can be a pain for this, especially if your data has been pulled in from some other file - the characters are hidden, so you can't see them, but they are there and so still cause a mismatch on the lookup.
To overcome this, clean your data first. Using your example above, use a spare column in each of your worksheets, and for each row with a value in column B, type "=clean(B2)", "=clean(B3)" and so on down (without the quotation marks). This will give you a clean version of the data: you can Copy and Paste Special the Values back into your original column B, and hopefully see the last of those #N/A's.
Cheers,
Bob.

 

[Gavona]

To ensure all numbers are recognised as numbers rather than text:

type 1 in a spare cell
Copy it
Select all your data
PasteSpecial Multiply

This will convert any numbers formatted as text to numbers.
Do this both on your lookup table and the main table.

Thanks,

Gavin

 

[KenWright]

One way with your data is to make everything text from within the formula, eg :-

=VLOOKUP(TRIM(A2),TEXT(MyTable,"@"),2,0)

but array entered using CTRL+SHIFT+ENTER.

This converts the lookup reference to text and the lookup table to text (within the formula only though). The downside is that a lot of arrays may have a performance effect on your spreadsheet.

Personally I prefer to clean the source data and ensure the data typing matches out.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------