How do I fit IF(ISBLANK) into these formulas?

[slam]

So I’ve got my spreadsheet functioning as I want in terms of results, but I need to tweak some formulas to account for blank cells so I don’t get #N/A errors. I’ve done this many times before with simpler formulas using =IF(ISBLANK()…. But am not sure how to apply it to some of the more complex formulas I’m using. I’ll just give you a couple, and hopefully I’ll figure out how to apply it to the other formulas with different functions etc.

=INDEX(Australia!$B$2:$B$17,MATCH(MIN(Australia!$G$2:$G$17),Australia!$G$2:$G$17,0))

=VLOOKUP($B3,Australia!$B$2:$H$17,7,FALSE)

In both formulas, there may or may not be data on the cells referenced on the Australia worksheet. How do I fit in the IF(ISBLANK() to them?

Thanks

 

[T. Valko]

So I’ve got my spreadsheet functioning as I want in terms of results, but I need to tweak some formulas to account for blank cells so I don’t get #N/A errors. I’ve done this many times before with simpler formulas using =IF(ISBLANK()…. But am not sure how to apply it to some of the more complex formulas I’m using. I’ll just give you a couple, and hopefully I’ll figure out how to apply it to the other formulas with different functions etc.

=INDEX(Australia!$B$2:$B$17,MATCH(MIN(Australia!$G$2:$G$17),Australia!$G$2:$G$17,0))

=VLOOKUP($B3,Australia!$B$2:$H$17,7,FALSE)

In both formulas, there may or may not be data on the cells referenced on the Australia worksheet. How do I fit in the IF(ISBLANK() to them?

Thanks

Error trapping is easier in Excel versions 2007 and later.

What version of Excel are you using?

 

[slam]

Error trapping is easier in Excel versions 2007 and later.

What version of Excel are you using?

2003 unfortunately. I have no control over upgrading - at work....

 

[T. Valko]

2003 unfortunately. I have no control over upgrading - at work....

OK, for the INDEX formula we can test to make sure there are numbers in the range like this:

=IF(COUNT(Australia!$G$2:$G$17),INDEX(Australia!$B$2:$B$17,MATCH(MIN(Australia!$G$2:$G$17),Australia!$G$2:$G$17,0)),"")

And for the VLOOKUP, we can do this:

=IF(ISNA(VLOOKUP($B3,Australia!$B$2:$H$17,7,0)),"",VLOOKUP($B3,Australia!$B$2:$H$17,7,0))

As you can see, we're repeating the VLOOKUP portion of the formula. This isn't very efficient. If the value to be returned is TEXT this version is a bit more efficient:

=LOOKUP("zzzzz",CHOOSE({1,2},"",VLOOKUP($B3,Australia!$B$2:$H$17,7,0)))

 

[slam]

OK, for the INDEX formula we can test to make sure there are numbers in the range like this:

=IF(COUNT(Australia!$G$2:$G$17),INDEX(Australia!$B$2:$B$17,MATCH(MIN(Australia!$G$2:$G$17),Australia!$G$2:$G$17,0)),"")

And for the VLOOKUP, we can do this:

=IF(ISNA(VLOOKUP($B3,Australia!$B$2:$H$17,7,0)),"",VLOOKUP($B3,Australia!$B$2:$H$17,7,0))

As you can see, we're repeating the VLOOKUP portion of the formula. This isn't very efficient. If the value to be returned is TEXT this version is a bit more efficient:

=LOOKUP("zzzzz",CHOOSE({1,2},"",VLOOKUP($B3,Australia!$B$2:$H$17,7,0)))

Excellent, thank you! Could be a mix of text or numbers for the 2nd one, so I went with your first formula. Thanks again!

 

[T. Valko]

Excellent, thank you! Could be a mix of text or numbers for the 2nd one, so I went with your first formula. Thanks again!

You're welcome. Thanks for the feedback!