Wednesday, May 23, 2007

Iserror

If you are obsessed with both formatting and efficiency, you just have to add iserror to your bag of tricks.

The way I use iserror is to keep those error messages off my spreadsheets. That isn't the only thing you can do with iserror, but it's how I use it.

Let's say that you have mastered VLOOKUP. But your two lists don't contain all of the same data. You don't really care about that. If your VLOOKUP doesn't find a value, that's OK. You just don't want to see an #N/A.

Here's what you do. First, build your VLOOKUP. GO ahead. Use the wizard. You know how.
=VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0)
Now, add the iserror:
=ISERROR(VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0))

This function is just asking a True or False Question. In this case, it is asking is my VLOOKUP returning a value or an error?

If it finds a value, the answer is NO or FALSE. If it finds no value and returns an error, the answer is YES or TRUE. You don't want to see TRUE or FALSE, though. You want to see a value or nothing. So, you need to use an IF.

=IF(ISERROR(VLOOKUP(A2,[Phone.xls]Sheet1!$A$2:$B$312,2,0)),"",VLOOKUP(A2,[Phone.xls]
Sheet1!$A$2:$B$312,2,0))

Now, the formula can be read like this:
If my VLOOKUP returns an error, enter a blank (""), otherwise, return the VLOOKUP result.

Although not slam dunk easy, if you take this in little chunks, it will work out just fine for you. And, it will save you time because you won't have to bother deleting the #N/A. So annoying.

5 comments:

Anonymous said...

Thank you, from Miami FL, very helpful guide!

WhoAmI said...

Thanks for making it easy to understand...

agarwal ankur said...

I got one value in each cell A1 and A2.
If value in A1 is found in the Column M of then the comment should be " Resolved", if not then the value in A2 should be checked in the column N.If it is found then the comment should be "Suspense". If A2 is not found in column M then the comment should be "Invalid".
Please advice how should I frame my formula in Excel. I have tries all combinations with IF,Vlookup and Iserror but in vain.

Anonymous said...

You probably figured it out by now but here it is :)

=IF(ISERROR(VLOOKUP(A1,M1:M1000,1,FALSE)),IF(ISERROR(VLOOKUP(A2,M1:M1000,1,FALSE)),"Invalid","Suspense"),"Resolved")

Markus Schopenknecht said...

Thanks for your professional guide. Only thing im missing is screenshot documnetation, but dont, worry, i am very much a newbie when it comes to excel, so that doesnt mean much. However, if i may, i would like to refer to a guide that has screenshots for step by step learning: excel iserror guide. Just remember, this appears to be very much a beginners guide, and not nearly as in depth as excel-experts blog.