iserror formula help

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
can anybody spot the error(s) in this formula? I'm trying to to a lookup to find a match on both the H and G columns with two columns on the DMB' F & T' tab, and return what is in column R if there is a match, or return nothing at all if there isn't.

Any help is much appreciated.


=if(iserror(vlookup(H19&G19,'DMB F & T'!'$A:$S,18,FALSE)),"",(vlookup(H19&G19,'DMB F & T'!'$A:$S,18,FALSE)))
 

Some videos you may like

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

pagrender

Well-known Member
Joined
Sep 3, 2008
Messages
652
Does this work? I removed the single quotes after the exclamation point...

=if(iserror(vlookup(H19&G19,'DMB F & T'!$A:$S,18,FALSE)),"",(vlookup(H19&G19,'DMB F & T'!$A:$S,18,FALSE)))
 

hiker95

Well-known Member
Joined
Apr 8, 2009
Messages
17,649
erniepoe,

Try (without the ' character in front of the $ character):

=if(iserror(vlookup(H19&G19,'DMB F & T'!$A:$S,18,FALSE)),"",(vlookup(H19&G19,'DMB F & T'!$A:$S,18,FALSE)))
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
Thanks guys,

the upgraded formula did work, but for some reason its returning a 0 when there is nothing in column R on the other page, rather than being blank.

is there any reason why this might be happening?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192

ADVERTISEMENT

can anybody spot the error(s) in this formula? I'm trying to to a lookup to find a match on both the H and G columns with two columns on the DMB' F & T' tab, and return what is in column R if there is a match, or return nothing at all if there isn't.

Any help is much appreciated.


=if(iserror(vlookup(H19&G19,'DMB F & T'!'$A:$S,18,FALSE)),"",(vlookup(H19&G19,'DMB F & T'!'$A:$S,18,FALSE)))

A faster approach...

If on Excel 2007 or later, invoke:

=IFERROR(VLOOKUP(H19&G19,'DMB F & T'!'$A:$S,18,0),"")

OTherwise:

a) If Vlookup is expected to return text...

=LOOKUP(REPT("z",255),CHOOSE({1,2},"",VLOOKUP(H19&G19,'DMB F & T'!'$A:$S,18,0)))

b) If Vlookup is expected to return a numeric value...

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},"",VLOOKUP(H19&G19,'DMB F & T'!'$A:$S,18,0)))
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
thanks Aladin,

I am on 2007 so your new formulas are very helpful, except I am still getting the return of 0 instead of nothing when the cell is blank.

I don't think it's a formula error, but rather maybe I've formatted in such a way to be causing this. Anything you can think of that might be causing this?
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623

ADVERTISEMENT

Thanks guys,

the upgraded formula did work, but for some reason its returning a 0 when there is nothing in column R on the other page, rather than being blank.

is there any reason why this might be happening?
If the lookup value is found but the cell in the return column is empty then that empty cell evaluates to 0. What result would you want? Is 0 an otherwise valid result?

What is the data type of the value being returned? Is is TEXT, NUMERIC could be either?

Also, what version of Excel are you using?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,192
thanks Aladin,

I am on 2007 so your new formulas are very helpful, except I am still getting the return of 0 instead of nothing when the cell is blank.

I don't think it's a formula error, but rather maybe I've formatted in such a way to be causing this. Anything you can think of that might be causing this?

If the result cell associated with a look up value is empty, VLOOKUP will return 0. If you want to see blank as result, try...

=IFERROR(IF(VLOOKUP(...)="","",VLOOKUP(...)),"")
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
If the lookup value is found but the cell in the return column is empty then that empty cell evaluates to 0. What result would you want? Is 0 an otherwise valid result?

What is the data type of the value being returned? Is is TEXT, NUMERIC could be either?

Also, what version of Excel are you using?



I'm using Excel 2007, and the data type would be text; either the letter L, or M, or nothing. I'd prefer that if column R contains a blank, that the return also be blank. Is this even possible, or should I just filter the 0's at the end and delete them?
 

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
If the result cell associated with a look up value is empty, VLOOKUP will return 0. If you want to see blank as result, try...

=IFERROR(IF(VLOOKUP(...)="","",VLOOKUP(...)),"")


Thanks Aladin,

that worked perfectly! Much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,880
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top