vlook

[Wanna Learn]

Hello I have a formula in a cell and the response is #NA. Here is the
formula
=VLOOKUP($J33,'2006 Discount Grid'!$A$1:$O$386,12,FALSE) the reason I'm
getting the #NA response is that $J33 is also a Vlookup. but I don't know
how to correct this. Can one of you geniuses help please and thanks

 

[Teethless mama]

=IF(ISNA(VLOOKUP($J33,'2006 Discount
Grid'!$A$1:$O$386,12,FALSE),"",=VLOOKUP($J33,'2006 Discount
Grid'!$A$1:$O$386,12,FALSE))

The above formula will trap the #N/A

 

[Jim Thomlinson]

Try this...

=if(isna($J33), "Error Message", VLOOKUP($J33,'2006 Discount
Grid'!$A$1:$O$386,12,FALSE))

 

[Wanna Learn]

Thanks "Teethless mama" I copied the formula exactly as it is below and it
does not work. Let me rephase. the value in cell J33 is also a lookup
formula

 

[vezerid]

Following the discussion until now, I have a couple of questions.
1. If I understand you correctly, you have pinpointed your problem down
to the formula returning #N/A because J33 itself is #N/A, J33 being the
result of a lookup which failed. Is this correct?
2. What do you mean by "correct"? Avoid the error value and return
blank or a message of choice? If J33 required a value to be found and
the value was not found it is reasonable that a query based on J33 will
return "not found" in any form.

If you want to trap the error of J33 then you could use
IF(ISNA(J33),a,b). But what could these values a, b be? Whatever they
are, either they will not be in your lookup table
('2006 Discount Grid'!$A$1:$O$386) or they could default to a certain
value (unlikely).

So I suggest you specify more clearly what it is you want to avoid.

HTH
Kostis Vezerides

 

[Nick Hodge]

That shouldn't matter, excel will read the value result of the vlookup, not
the formula itself.

You have another issue producing the #N/A

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England

(e-mail address removed)
www.nickhodge.co.uk

 

[Wanna Learn]

Thank you everyone . I could not have done it without you. J 33 was a
result of a lookup -and that table was formated as text - and the discount
grid table was formatted as number doh! again thanks everyone