vlookup
Posted by Forenzix on August 27, 2001 2:34 AM
Hi,
I'm creating an invoice worksheet that populates cells
with VLOOKUP. Now I don't want Excel to show #N/A when the value is not found. How do I do that?
I currently use the following formula :
=IF(A17="";"";VLOOKUP(A17;Items!A5:I152;2;FALSE))
if A17 is empty, he shows the empty cell
if there is something in A17 Excel will go to the Items sheet to find the corresonding item and show it in the cell. However, when he does not find the item, he will show #N/A. I've tried different approaches to do this, but none worked...
I'm desperate.
Thx,
WR
Posted by Forenzix on August 27, 2001 3:00 AM
found the solution and thought I should share it with y'all ...
=IF($A15="";0;IF(ISERROR(VLOOKUP($A15;Items!$A$5:$I$150;3;FALSE));0;VLOOKUP($A15;Items!$A$5:$I$152;3;FALSE)))
A shorter solution is always welcome :)
Posted by Aladin Akyurek on August 27, 2001 3:40 AM
Not much shorter, but nicer I hope:
=IF($A15="";0;IF(ISNUMBER(MATCH($A15;Items!$A$5:$A$152;0));VLOOKUP($A15;Items!$A$5:$I$152;3;0)))
0 means FALSE.
You could also name the range A5:A152 LVALUES (from Lookup Values)and the range A5:I152 LTABLE (from Lookup Table) via the Name Box. Using these names the formula a bit shorter too:
=IF($A15="";0;IF(ISNUMBER(MATCH($A15;LVALUES;0));VLOOKUP($A15;LTABLE;3;0)))
Aladin
