Index Match Multiple Variables Display 0 Instead of #N/A

[zimbie_z]

Here is my formula.

These cells contain text
Cell K3 and Positions!$C$2:$C$2500

If there is no match then the formula returns #N/A. I need it to return 0 instead of #N/A.

I have tried several different combination's but cannot get it to return 0.

{=INDEX(Positions!$F$2:$F$2500,MATCH($J$2&K3,Positions!$A$2:$A$2500&Positions!$C$2:$C$2500,0))}

Any Help would be appreciated.

 

[PCL]

Perhaps

=IF(ISERROR(INDEX(Positions!$F$2:$F$10,MATCH(K3,Positions!$A$2:$A$10&Positions!$C$2:$C$10,0))),0,INDEX(Positions!$F$2:$F$10,MATCH(K3,Positions!$A$2:$A$10&Positions!$C$2:$C$10,0)))

Of course confirm with Contol + Shift + Enter
Adapt it to your need

 

[mgirvin]

Dear zimbie z,

If you have Excel 2007, then this will work:

=IFERROR(INDEX(Positions!$F$2:$F$2500,MATCH($J$2&K3,Positions!$A$2:$A$2500&Positions!$C$2:$C$2500,0)),0)

Ctrl + Shift + Enter.

For huge spreadhseets, the IFERROR is much faster.

 

[zimbie_z]

Thanks to both of you.

I do not have office 2007 so I had to use PCL's formula. With one modification.

PCL left out my second variable so this is the formula that works.

{=IF(ISERROR(INDEX(Positions!$F$2:$F$2500,MATCH($J$2&K3,Positions!$A$2:$A$2500&Positions!$C$2:$C$2500,0))),0,INDEX(Positions!$F$2:$F$2500,MATCH($J$2&K3,Positions!$A$2:$A$2500&Positions!$C$2:$C$2500,0)))}

 

[mgirvin]

Dear Dear zimbie z,

Excel 2007 is worth getting. There are some amazing new things that make spreadsheets for efficient.

IFERROR is convenient, not just for its speed, but also because it makes the formula less confusing and faster to type.

Another example of Excel 2007 EXCELlence is the SUMIFS function. If you have a huge spreadsheet SUMIFS can be about 100% faster (depending on size, it can be a lot more) than a comparable SUMPRODUCT formula.

That is not to mention the great new filter options and the amazing new table abilities also.

 

[Aladin Akyurek]

Thanks to both of you.

I do not have office 2007 so I had to use PCL's formula. With one modification.

PCL left out my second variable so this is the formula that works.

{=IF(ISERROR(INDEX(Positions!$F$2:$F$2500,
MATCH($J$2&K3,Positions!$A$2:$A$2500&Positions!$C$2:$C$2500,0))),0,
INDEX(Positions!$F$2:$F$2500,MATCH($J$2&K3,
Positions!$A$2:$A$2500&Positions!$C$2:$C$2500,0)))}

The following should be faster...

Control+shift+enter:

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
   INDEX(Positions!$F$2:$F$2500,MATCH(1,
    IF(Positions!$A$2:$A$2500=$J$2,
    IF(Positions!$C$2:$C$2500=K3,1)),0))))