Prevent VLOOKUP formula from returning an error when exact match is not found.

**ExcelTip** · 05-11-2005, 08:24 AM

Problem:

Creating a formula that will check whether each number in column B appears in List 1 (Column A).
When finding a number in List1, the formula will return that number.
If an exact match is not found, the formula will not return an error, but a text indicating that the value was not found.

Solution:

To check whether a match was found by the VLOOKUP function, use the ISERROR function in the following IF statement:
=IF(ISERROR(VLOOKUP(B2,$A$2:$A$9,1,FALSE)),\"\"Number Not Found\"\",VLOOKUP(B2,$A$2:$A$9,1,FALSE))
Thus, in case an exact match was not found, the formula will return \"\"Number Not Found\"\", instead of an error.

List1__Numbers to Check____Result
8______2___________________Number Not Found
5______7___________________7
3______10__________________Number Not Found
7______6___________________6
4
9
6
1

**Baganini** · 05-19-2005, 09:42 AM

Or You Can Simply Type:

If(countif(a$2:a$9,b2)=1,vlookup(b2,$a$2:$a$9,1,false)," ")

**Alan** · 05-25-2005, 01:14 AM

Hi Baganini,

Originally Posted by Baganini

Or You Can Simply Type:

If(countif(a$2:a$9,b2)=1,vlookup(b2,$a$2:$a$9,1,false)," ")

I think that does something slightly different - but useful nevertheless.

The first one at the top of this thread, avoids an error where there is no exact match.

Yours returns a space character if there is EITHER no match OR more than one match.

Good to have both options available!

Thanks,

Alan.

**David Watson** · 04-25-2007, 08:27 AM

Hi, supporting a large team of data analysts, who use Excel extensively, I frequently have to counsel Excel users hit by VLOOKUP misery.

Often, it's not simply a case of reporting something other than the unhelpful #N/A, but it's a matter of cleaning the data before a match is found (frustrating when you can see the data, but VLOOKUP can't find it).
Interestingly, countif sometimes (often) can find data that VLOOKUP can't ...

The number of causes of #N/A or #REF appearing are huge, but it's usually down to around 10 different common causes which can be fixed using additional functions such as VAL, or TEXT.

Rather than repeat the same advice over and over, to help with this I've created an add-in which does the job for you. Click on a cell with a VLOOKUP that is returning #N/A and it will usually suggest a modified function for you, copy it to the clipboard for you, and it's ready to paste into your spreadsheet. It will also offer you a function wrapped inside IF(ISERROR(... to save you from typing this up yourself.

The tool is at ; http://www.add-innovation.co.uk and click on "Free Add-Ins".

It can even suggest matches for dates stored as numbers but formatted as dates, being looked up against text dates that look the same.

Part of the way the tool works is to look at how many instances of the value it can find using COUNTIF - if countif returns a value, and VLOOKUP can't, then it tries to find the cause.

Please let me know what you think.

Prevent VLOOKUP formula from returning an error when exact match is not found.

LinkBack

Thread Tools

Display

Prevent VLOOKUP formula from returning an error when exact match is not found.

Reply: Baganini

VLOOKUP Errors.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions