Excel 2007 : trouble with vlookup and ISNA formula

**Gweny** · 07-05-2012, 02:07 PM

I’m using Excel 2007. When I do a vlookup it returns #N/A if the cell is empty. I want the formula to return “0” if the cell is empty. I have attached a spreadsheet. Here is the formula that I wrote in cell B3 through B7:
=IF(ISNA(VLOOKUP($A3,$E$3:$H$6,4,FALSE)),0,VLOOKUP($A3,$E$3:$H$6,4,FALSE))

The problem that I’m having is that there are two similar account names in my data so the result is picking up the results from a similar account name instead of giving me “0”. The two similar accounts names are ** LIFO Inventory Reserve and 16900010 - LIFO Inventory Reserve. The result for ** LIFO Inventory Reserve is incorrect (cell B6). The results should be “0”. Instead it is picking up “PM” for account 16900010 - LIFO Inventory Reserve (cell H5). Why is my formula picking up “PM”? I can’t change the account names. How can I write the formula so that it picks up “0”?
Help is appreciated. Thanks

**NBVC** · 07-05-2012, 02:13 PM

That is because "*" is a wildcard character for VLOOKUP() function..

Try =IF(ISNA(VLOOKUP("~"&$A3,$E$3:$H$6,4,FALSE)),0,VLOOKUP("~"&$A3,$E$3:$H$6,4,FALSE)) where the ~ cancels the wildcard.

In Excel 2007, this might also work:

=IFERROR(VLOOKUP("~"&$A3,$E$3:$H$6,4,FALSE),0)

**mshale** · 07-05-2012, 02:22 PM

Well, that's because the value you are searching for contains an asterisk (*). Excel treats these as wildcards, and so it's finding anything with "LIFO" in the cell.

As for a workaround, try a tilde (~) before the lookup value.

*EDIT* they need to invent insta-refresing forums lol.