iserror formula help

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
336
Office Version
  1. 365
Platform
  1. Windows
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)))
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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
336
Office Version
  1. 365
Platform
  1. Windows
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,210

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
336
Office Version
  1. 365
Platform
  1. Windows
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,210
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
336
Office Version
  1. 365
Platform
  1. Windows
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
336
Office Version
  1. 365
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,487
Messages
5,831,972
Members
430,099
Latest member
rdhoy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top