Stumped with VLOOKUP error

[bactfarmer]

Why do I get a #NA error when I do a VLOOKUP on a cell that contains a
formula? Is this because its trying do the lookup on the formula
itself and not the data displayed in the cell due to the formula?

=VLOOKUP(E2,PRODTAB,3,FALSE) Where (E2) contains a formula that
processes information from another cell.

I know every thing else is ok because when I type in the display
formula result from (E2) into a (E3) and change the VLOOKUP to
=VLOOKUP(E3,PRODTAB,3,FALSE) it works.

Thanks
Very Confused

 

[Guest]

My guess would be that your formula in E2 is returning a text value and your
lookup table (PRODTAB) contains numbers. Or vice versa.

The number 1234 and the text string 1234 may look identical to you and me,
but Excel treats them differently.

See if this makes any difference:

=VLOOKUP(VALUE(E2),PRODTAB,3,FALSE)

If not, post back with more info on your formulas and data.

HTH,
Elkar

 

[bactfarmer]

Works great! Thanks Elkar.

My guess would be that your formula in E2 is returning a text value and your
lookup table (PRODTAB) contains numbers. Or vice versa.

The number 1234 and the text string 1234 may look identical to you and me,
but Excel treats them differently.

See if this makes any difference:

=VLOOKUP(VALUE(E2),PRODTAB,3,FALSE)

If not, post back with more info on your formulas and data.

HTH,
Elkar