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

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?

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

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

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!

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?

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!!!

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!

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

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

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!