INDEX/MATCH formula doesn't like blank cells.

Here is the formula I'm working with:

=INDEX(Calcs!$AB$5:$AB$200,MATCH(1,INDEX((Calcs!$AB$5:$AB$200<>"")*(Calcs!$Z$5:$Z$200=$B20),),0))

It works perfectly except when it comes to end of the rows of data and encounters blank cells, it then spits out the dreaded "#N/A" error. Since the data slowly fills as the year goes on, I cannot simply limit the cells it calculates.

How can I change the formula to not freak-out when encountering blank cells?

Thanks in advance.

If you're simply trying to alleviate the #N/A error, you could use IFERROR, or IF(ISERROR if you are working with Excel 2003:

Please Login or Register  to view this content.

Please Login or Register  to view this content.