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

zimbie_z

New Member
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.

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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

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.

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

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.

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:

Code:
``````=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))))``````

Replies
7
Views
171
Replies
4
Views
300
Replies
5
Views
724
Replies
2
Views
66
Replies
7
Views
238

1,196,229
Messages
6,014,129
Members
441,804
Latest member
oifaejw

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back