Possible to add 'ISNA' to =IF(ISERROR(VLOOKUP string?

[aumdean]

Hi guys, I know this must be possible, I'm just not ordering something correctly...

Formula in question:

{=IF(ISERROR(VLOOKUP(A7,Sheet1!$A$5:$Q$105,15,FALSE)),"",VLOOKUP(A7,Sheet1!$A$5:$Q$105,15,FALSE))}

The above formula is returning values on Sheet2 in column I7:I67. However, the VLOOKUP may only return 15 rows from Sheet 1, therefore leaving an error in the remaining 45 rows. To hide the errors, I am using the ISERROR function. This formula has been working great, until recently. Some of the cells on Sheet1 are now blank, which was not the case previously. Therefore, the above formula is returning zeros now in place of the blank cells. If the cell on Sheet1 is blank, I want it to display as a blank in column I7:I67 on Sheet2, rather than a '0'.

So, in order to do this I have attempted to add the 'ISNA' function to the string above, unsuccessfully, to hide the '0' values. Or, is there an alternative method of hiding both zeros and errors from VLOOKUP? Can any of you help?

I can't use VBscript for this, I just need some tweaking to the above formula.

 

[GDH]

Hi

Could the following not work?

=IFERROR(VLOOKUP(A7,Sheet1!$A$5:$Q$105,15,FALSE), "")

 

[aumdean]

Hi

Could the following not work?

=IFERROR(VLOOKUP(A7,Sheet1!$A$5:$Q$105,15,FALSE), "")

This formula does, in fact, do the exact same thing as the original formula posted. Therefore, '0' is being returned for blank cells from Sheet1.

 

[GDH]

And with the formula below?

=IF(IFERROR(VLOOKUP(A7,Sheet1!$A$5:$Q$105,15,FALSE), "")=0, IFERROR(VLOOKUP(A7,Sheet1!$A$5:$Q$105,15,FALSE), ""),"")

 

[aumdean]

And with the formula below?

=IF(IFERROR(VLOOKUP(A7,Sheet1!$A$5:$Q$105,15,FALSE), "")=0, IFERROR(VLOOKUP(A7,Sheet1!$A$5:$Q$105,15,FALSE), ""),"")

Oddly, using this formula, nothing is displayed whatsoever, the cells are completely blank?

 

[GDH]

Sorry - don't know what I was trying with that! I hope this 1 is better!

=IFERROR(IF(OR(VLOOKUP(C10,$A$10:$B$12,2,0)=0,ISERROR(VLOOKUP(C10,$A$10:$B$12,2,0)=1)),"",(VLOOKUP(C10,$A$10:$B$12,2,0))),"")

 

[GDH]

The OR function is actually redundant because of the IFERROR at the beginning... This one is slighly more efficient.

=IFERROR(IF(VLOOKUP(C10,$A$10:$B$12,2,0)=0,"",(VLOOKUP(C10,$A$10:$B$12,2,0))),"")

 

[aumdean]

The OR function is actually redundant because of the IFERROR at the beginning... This one is slighly more efficient.

=IFERROR(IF(VLOOKUP(C10,$A$10:$B$12,2,0)=0,"",(VLOOKUP(C10,$A$10:$B$12,2,0))),"")

Thank you very much, the above formula does exactly what I needed!

 

[Aladin Akyurek]

Try...

=IFERROR(T(VLOOKUP(C10,$A$10:$B$12,2,0)),"")

if VLOOKUP is expected to return non-blank text values.

 

[GDH]

Thanks Aladin,

Did not know about that T function! Handy little trick to make the formula far more easy to write.