Excel 2000: Lookup Formula Returns #N/A 2

[LINSEY]

Here's the layout of my spreadsheet:

STYLE[tab][tab]FINISH[tab]SATURATOR
LYW329[tab][tab]3[tab][tab][tab]FERN
PRM685[tab][tab]6[tab][tab][tab]BLACK
KXWJ95[tab][tab]J[tab][tab][tab]YELLOW
NOM319[tab][tab]3[tab][tab][tab]FERN

Formula for "FINISH" column is: MID(STYLE,4,1)

Formula for "SATURATOR" column is: LOOKUP(FINISH,CODES!A:A,CODES!B:B)

Layout for lookup sheet,"CODES", which is in the same workbook:

FINISH[tab][tab]SATURATOR
0[tab][tab][tab][tab]RIGID
3[tab][tab][tab][tab]FERN
6[tab][tab][tab][tab]BLACK
8[tab][tab][tab][tab]SANGRIA
A[tab][tab][tab][tab]AVACADO
J[tab][tab][tab][tab]YELLOW

My problem is that no matter how the cells are formatted (number, text, general), if the "FINISH" column in the main spreadsheet is a number then the "SATURATOR" column returns "#N/A". BUT if the "FINISH" column in the main spreadsheet is a letter then the "SATURATOR" column returns the correct value.

Please help!
Linsey

 

[DaleWatson123321]

Hi Linsey,

I used the following formula, and it works...
=VLOOKUP(B2,CodeTable,2,FALSE)

A couple of important factors here, though...

1) Format your the first column of your "codes" range as "TEXT".

2) Be sure that the cells with your =MID(A2,4,1) formulas is formatted as "NUMBER".

This should work, as it worked for me. Please advise as to how you make out.

In case you're not familiar with assigning range names (like "CodeTable&quot, the method I recommend is:
a) Highlight the cell, or range-of-cells in this case
b) Hold down the <Control> key and hit the <F3> key
c) Type the name
d) Hit <Enter>

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[bluedragon2]

If formating is a problem with those fields, this formula should work too:

=IF(ISNUMBER(VALUE(B2)),LOOKUP(VALUE(B2),CODES!A:A,CODES!B:B),LOOKUP(T(B2),CODES!A:A,CODES!B:B))

Blue

If I wasn't Blue, I would just be a Dragon...

 

[LINSEY]

Dale,

I'm not very familiar with the VLOOKUP function. I tried to assign the range name like you described, but CTRL F3 just brings up my task manager. Is there a reason why a regular LOOKUP function will not work?

Linsey

 

[DaleWatson123321]

Linsey,

In place of <Control> <F3>, you can use the Menu: Insert - Name - Define.

If you like, I can email you the file. Just email me and I'll send it via return email.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[LINSEY]

Thanks Blue Dragon!!! That formula works great!

I appreciate your help also Dale and I will try assigning a range name just in case I need to use the VLOOKUP next time!

Linsey

 

[DaleWatson123321]

LINSEY,

You mentioned that you're not familiar with the VLOOKUP function. It's an important function to consider using for this specific reason. The &quot;False&quot; argument (that I included in my example) instructs Excel to &quot;force&quot; an EXACT MATCH.

If you don't require an EXACT match from your Codes list, then perhaps the LOOKUP function is fine.

But after testing Blue's formula, I've concluded that... in addition to not providing an EXACT match, it can provide INACCURATE results.

Perhaps Blue's formula can provide accurate results IF the data in the Code table is sorted in a particular order. I must admit I haven't been using the LOOKUP function, so perhaps I've missed something. All I know, is that I copied Blue's formula and the results are &quot;not to my liking&quot; (not accurate).

With the VLOOKUP function and the &quot;False&quot; argument, the data does NOT have to be in any particular order.

With the VLOOKUP function, IF the number or letter being looked up is NOT in the Code table, then the formula will (correctly) return: #N/A - which I suspect is what you would prefer.

So if you want to give this formula another try...
=VLOOKUP(B2,CodeTable,2,FALSE)

...first go to your Code sheet, and use these steps to assign the range name &quot;CodeTable&quot;...

1) Highlight Columns A and B (the ENTIRE columns) - i.e. click on the letter &quot;A&quot;, hold down <Shift>, and click on &quot;B&quot;.

2) Use Menu: Insert - Name - Define, and then type the name: CodeTable, and click OK.

3) Now enter or copy my formula.

Note: The &quot;2&quot; in the formula refers to the 2nd column within the range named CodeTable.

Hope this helps. Please advise if you're able to duplicate the results I achieved. As mentioned, I can still email you the file - now modified to show BOTH types of formulas.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[LINSEY]

Dale,

I tried the formula again. No problems defining the range this time, BUT I am still getting #N/A when the &quot;FINISH&quot; is a number.

I formatted the &quot;FINISH&quot; column to numbers.

Any idea what the problem is?

Linsey

 

[LINSEY]

Dale,

I think I figured it out...I combined the VLOOKUP with BlueDragon's formula. Seems to be working. Let me know if you see any other problems I may have missed.

=IF(ISNUMBER(VALUE(B2)),VLOOKUP(VALUE(B2),CODES,2,0),VLOOKUP(T(B2),CODES,2,0))

Thanks for your help!

Linsey