MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLOOKUP


Posted by Scott on January 07, 2002 1:38 PM

I am attempting to pull select data out of a large pool of data. I want to be able to look up an item number and get it's accompanying information. However, there are times when the item # I'm looking for doesn't correspond, so I get "N/A". Is there any way the get either a "0" or " " instead of the "N/A". It fouls up all other formulas.


Posted by Aladin Akyurek on January 07, 2002 1:46 PM

=IF(COUNTIF(E2:E100,A1),VLOOKUP(A1,E2:H100,2,0),0)

where E2:E100 is the first column of the table in E2:H100.
Adapt it to your ranges.

Aladin

Posted by Richard S on January 07, 2002 1:47 PM

Try =IF(ISNA(VLOOKUP(C6,B2:C4,2)),"",VLOOKUP(C6,B2:C4,2)), substituting the various arguments for your lookup array etc.