iserror formula help

[erniepoe]

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

 

[pagrender]

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]

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]

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]

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]

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]

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]

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]

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]

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.