MATCH/INDEX Formula Returns an Error Instead of 0

**livifivil** · 02-07-2013, 04:03 PM

I have a formula that adds up corresponding numbers on a pivot table by referencing the column and row headers. I want this formula to return a value of 0 when it cannot locate the row header. Because this formula works off a pivot table, sometimes the row headers are there and sometimes they are not. I have a section of the formula below. The row header in this formula is "Confirmed" so when a row starting with "Confirmed" does not show on the pivot table, I want it to return a value of 0 instead of saying that it cannot locate the row. Any help would be greatly appreciated, thanks in advance.

=INDEX(Sheet1!$A$1:$Z$98,MATCH("Confirmed",Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$1:$1,0))

**daddylonglegs** · 02-07-2013, 04:05 PM

Which version of Excel are you using? In Excel 2007 or later you can use IFERROR function to return an alternative to an error, i.e.

=IFERROR(INDEX(Sheet1!$A$1:$Z$98,MATCH("Confirmed",Sheet1!$A:$A,0),MATCH(B$1,Sheet1!$1:$1,0)),0)

**livifivil** · 02-07-2013, 04:11 PM

I am actually using Google Docs. I hate it. I usually use Excel 2007 but my office does not have a cloud service so, to share data, we went the cheap way. I know a lot of the formulas do not exist there but I figured some of the more simple formulas would be fine. I didn't even think about that but I am pretty sure that will work. This is only one section of the formula but it is basically the same thing over and over. Thanks, I am going to try that now.