Search TeachExcel.com
TeachExcel.com
TE
Teach Excel MS Office Tutorials Excel Consulting Services Excel Forum
Excel Video Tutorials Excel Tips Free Excel Macros Excel Help Resources Contact TeachExcel
Video Tutorials
  • Free Macros
  • Excel Help Directory
  • Excel 2007 Resources
  • Keyboard Shortcuts
  • Excel Forum
  • Contact/About

Tutorials: Lookup & Search Functions in Excel



Featured Tutorial
Dynamically Update Drop-Dowm Menus in Excel
Featured Macro
Delete Empty Rows in Excel

Excel Lookup/Search Tip 5 - Vlookup - IFERROR with Vlookup to Assign Values Instead of Errors

Video | Similar Helpful Excel Resources

Bookmark and Share

Use the new IFERROR() function in Excel 2007 in conjunction with the VLOOKUP() function in order to never have to see another error displayed in Excel. The IFERROR() function allows you to display whatever default value you want in a cell if the original formula returns an error. This is an example of a nested function or formula since the Vlookup function is actually placed inside of the Iferror function. The IFERROR function is a great addition to Excel 2007 and should be learned by everyone who uses Excel.
   Topics Covered
IFERROR() Function in Excel.
VLOOKUP() Function in Excel.
Assign default values instead of errors for functions in Excel.
Quickly remove errors from functions and formulas in Excel 2007.
   Difficulty:         Easy
   Video:               Yes



Got a Question? Ask it Here in the Forum.



Similar Helpful Excel Resources

If(iserror(vlookup) Vs Iferror(vlookup - Excel

View Content
I was under the impression that IFERROR(VLOOKUP would be able to replace IF(ISERROR(VLOOKUP. ... Yet when I use IFERROR(VLOOKUP I get a different result than if I use IF(ISERROR(VLOOKUP. Am I wrong?

Correct results should be:
ABO Jan 09 0.3% Feb 09 11.0% QTD 2/28/09 5.7%

Here are the two formulas:

=IFERROR(VLOOKUP($B6,'[Sales By Day 20090305.xls]ACA E'!$I$400:$DJ$402,C$3),"")
Using this, I see:

ABO Jan 09 11.0% Feb 09 11.0% QTD 2/28/09 5.7%


=IF(ISERROR(VLOOKUP($B5,'[Sales By Day 20090305.xls]ACA E'!I$400:DJ$402,1,)),0,VLOOKUP($B5,'[Sales By Day 20090305.xls]ACA E'!I$400:DJ$402,C$3,))

I do obtain the correct result with this one, but I wanted to use the IFERROR(VLOOKUP to eliminate the double formula as mentioned in the Mr. Excel book. I'm stumped because it returns FEB and QTD numbers correctly but Jan is incorrect. Yes, the data range is correct with $I$400:$DJ$402 as Jan is row 400, Feb is 401 and QTD is 402.

Am I using it (it being the IFERROR(VLOOKUP(... ) incorrectly?

Vlookup And Iferror Help Soon... - Excel

View Content
Create a worksheet that allows a person to enter in an age and a ticket location, and automatically gives the price of the ticket.

If the user enters an illegal location the worksheet should put an error message in the cell.


Cant figure out the formula
i have made the lookup table and columns are sorted in order

i used a scroll bar for the age and a radio button for the 4 ticket locations.

Please help driving me nuts

Iferror With Vlookup - Excel

View Content
Hi All,

I need to change / add to the following formula...

=IFERROR(VLOOKUP(F2,'GLASS CSV REPORT DUMP'!$A$1:$BX$150,61,FALSE),"")

What I need it to do is look at cell C2 and if the text that is in C2 starts with a Z then return what is in 'GLASS CSV REPORT DUMP'!$A$1:$BX$150, 60 ,FALSE)

If the text in C2 does not start with a Z then return what is in 'GLASS CSV REPORT DUMP'!$A$1:$BX$150, 61 ,FALSE),"")

Hope that makes sence!

Thanks

Iferror Vlookup Help!!!!! - Excel

View Content
I am desperately trying to write a formula that would lookup B5 & BC on a worksheet named "LookupPivots" to find cost between H:K in the 3rd position

So far I have this:
=IFERROR(VLOOKUP(B5,LookupPivots!$H:$K,3,0)+(VLOOKUP(C5,LookupPivots!$H:$K,3,0)),"0")

Which is returning 0 number. Since the second lookup does not have costs asscoiated, but the first look up does have cost. I know I have the value if error wrong, but I am not sure how to fix it.

I need the formula to only return 0 number IF both lookups do not have data. But if one does then to return that number or add both and return that number.

What in the world am I missing.

Please help!

Vlookup/iferror In Charts - Excel

View Content
Hey

I'm having trouble making a chart with the iferror and vlookup formulas.

This is what the formula looks like =iferror(vlookup(=IFERROR(VLOOKUP(G4,$B$4:$C$41,2,0),"")

It looks for the G4 in the table B4:C41, giving back the 2nd column value. If there is an error (like no data for G4), it gives "" (blank).

What I need is for the line chart to show blanks where there is no data to be found. But instead it shows zero, so the line goes all the way down and up again. If you don't understand what I'm talking about, say so. I'll post an attachment.

Any ideas how I solve this in a simple way?

Dynamic Pivot Table Lists W Iferror/vlookup For Comments - Excel

View Content
Many days and hours later, I've searched and searched but cannot figure out how to use the iferror or/and vlookup combination to pull ONLY comments (this is from data only worksheet but includes values AND comments)

I would use a dynamic pivot table list of names then change list by dates... then I want the rest to pull specific comments.

Can anyone help? Without using macros/vba/coding?

Gracias!!!

Use Vlookup To Get Values, Discard Vlookup Function But Retain Sought Values - Excel

View Content
OK I have used vlookup to get values from a different workbook for a while large column. It works fine, thing is now I only want the value it sought and not the formula itself, i.e. if I change the values in the second workbook I do NOT want the values to be updated in the vlookup column. How do I discard the formula and yet retain the values?

Thanks!

Vlookup Without Errors - Excel

View Content
How do I modify my vlookup formula so that if is returns #n/a, it will be replaced with a hyphen rather than the #n/a error?

Thanks again,
Carlos

Vlookup Errors - Excel

View Content
Hi! Experts

I have a table
for eg See the Attached

In Sheet " Master" column A contains CustCode. In Sheet" Vlookup" Column A contains another CustCode.
when i give a vlookup in a different sheet to check for PropNo. in column B . The first cell works fine, but for the second cell it returns the Same value

Is there a way to get round this

Thanks in advance

Hardeep Kanwar

Vlookup Possible Errors - Excel

View Content
hi,

i am trying to use VLOOKUP to populate a cell's value. I noticed that i get different types of error fields populated when a value is wrong/not found. Can someone explain to me the reasons why we get these different types of error fields:

#N/A
#NAME?
#REF?
=VLOOKUP(B2,F:G,2,FALSE)

in the 4th case, Im sure that a value is present in the column where i am looking in but the formula doesn't seem to have gotten executed. It just displays the formula the same way.

Appreciate the help.

Thanks!

Contact | Privacy Policy | Disclaimer
Copyright© 2012 TeachExcel.com