iserror formula help

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
380
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)
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)))
 
Upvote 0
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)))
 
Upvote 0
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?
 
Upvote 0
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)))
 
Upvote 0
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?
 
Upvote 0
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?
 
Upvote 0
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(...)),"")
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,586
Messages
6,179,712
Members
452,939
Latest member
WCrawford

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
Back
Top