asked on

vlookup help

folks

I have a vlookup

VLOOKUP(A1,Sheet2!B1:C6,2,FALSE)

how can i instead of showing N/A when the condition is false rather state 'NOT FOUND'

all help will do

ASKER CERTIFIED SOLUTION

Simon

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Monika Bharti

Hi,

To return N/A when the condition is false with any error from the VLOOKUP (e.g. including if B2 is populated but that value isn't found by the VLOOKUP) you can use IFERROR function, if you have Excel 2007.

=IFERROR(VLOOKUP(B2,Index!A1:B12,2,FALSE),"N/A")

Open in new window

In earlier versions you need to repeat the VLOOKUP, e.g.

=IF(ISNA(VLOOKUP(B2,Index!A1:B12,2,FALSE)),"N/A",VLOOKUP(B2,Index!A1:B12,2,FALSE))

Open in new window

Note: Replace N/A if you want to change this in other string which you want.

Matt Dunn

You might be expecting that not all of your search values are going to return something from the search table, in which case you can use the iferror function.

It works like this

= iferror (YourVlookupFormula, WhatToSayInsteadOf#N/A)

Here’s an example

=iferror(vlookup(D3,A:C,3,false), “Not Found”)

Or if you would rather it was just blank then instead of having No Value Found, just have the two sets of inverted commas, like this

=iferror(vlookup(D3,A:C,3,false), “”)

(Source: http://www.excelvlookuphelp.com/i-dont-want-to-see-na-if-the-lookup-value-isnt-found/)