MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Question


Posted by David Williams on June 17, 2001 8:11 PM

=VLOOKUP(D1,K1:L30,2,FALSE)

I enter a number from 0-29 into D1 the codes shows up in other cell but if I leave d1 blank the other cell G still shows the codes for the number 0. K column is numbers and L column is the code txt for the numbers I am sure this is function formula but how can I have the cell show no value if D1 is blank? I want to be able to hide K&L and it show up in G which it is doing right now except I need to have d blank and G show no valuesif no input. Hope I explained this well enough.
thanks


Posted by Aladin Akyurek on June 17, 2001 11:36 PM

Try

=IF(ISNUMBER(MATCH(D1,K1:K30,0)),VLOOKUP(D1,K1:L30,2,0),"")

I substituted 0 for FALSE, because 0=FALSE.

Aladin

==============

Posted by Aladin Akyurek on June 17, 2001 11:42 PM

Upon a closer reading of the question, I'd suggest:

=IF(ISBLANK(D1),"",VLOOKUP(D1,K1:L30,2,0)

or

=IF(ISBLANK(D1),"",IF(ISNUMBER(MATCH(D1,K1:K30,0)),VLOOKUP(D1,K1:L30,2,0),""))

Aladin

Posted by David Williams on June 20, 2001 2:48 PM

Aladin Akyurek

Thanks Aladin,
It worked great