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
rutgermonsAsked:
Who is Participating?
 
SimonConnect With a Mentor Commented:
=IFERROR(VLOOKUP(A1,Sheet2!B1:C6,2,FALSE),"NOT FOUND")
0
 
Monika BhartiSr. AnalyticsCommented:
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.
0
 
Matt DunnCommented:
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/)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.