2 D look up Index Match across Multiple Sheets i.e. If not in S1, then S2, If not S2 then S3.

[questforexcel]

Hi All,

I know how to use the Index and Match functions for pulling out data from a matrix. Could you please help and advise on how to combine multiple Index Match functions for one cell across multiple sheets of matrix. I searched online couldnt find any solution to this.

What I need my data to do is search for the combinations of the matrix in Sheet1, if not in Sheet1 then search for it in Sheet 2, if not in Sheet 2 then search for it in Sheet 3.

Below is the formula for the single index match function which works fine, but when i insert another index match function seperated by a "," from the 1st one, it just shows#NA. Could you please help. Thank you

=IFERROR(INDEX('FY 16 P&L'!$C$6:$O$61,MATCH(' MoM P&L FY16-FY18'!$B6,'FY 16 P&L'!$B$6:$B$61,0),MATCH(' MoM P&L FY16-FY18'!C$4,'FY 16 P&L'!$C$5:$N$5,0)),"")

[TABLE="width: 344"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD]Particulars[/TD]
[TD="align: right"]Sep-15[/TD]
[TD="align: right"]Oct-15[/TD]
[TD="align: right"]Nov-15[/TD]
[/TR]
[TR]
[TD]Total Income [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sales[/TD]
[TD="align: right"]

[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Repeat the iferror, changing the sheet ref on each to be the next sheet (note i haven't changed the ref's).

=IFERROR(INDEX('FY 16 P&L'!$C$6:$O$61,MATCH(' MoM P&L FY16-FY18'!$B6,'FY 16 P&L'!$B$6:$B$61,0),MATCH(' MoM P&L FY16-FY18'!C$4,'FY 16 P&L'!$C$5:$N$5,0)),IFERROR(INDEX('FY 16 P&L'!$C$6:$O$61,MATCH(' MoM P&L FY16-FY18'!$B6,'FY 16 P&L'!$B$6:$B$61,0),MATCH(' MoM P&L FY16-FY18'!C$4,'FY 16 P&L'!$C$5:$N$5,0)),IFERROR(INDEX('FY 16 P&L'!$C$6:$O$61,MATCH(' MoM P&L FY16-FY18'!$B6,'FY 16 P&L'!$B$6:$B$61,0),MATCH(' MoM P&L FY16-FY18'!C$4,'FY 16 P&L'!$C$5:$N$5,0)),IFERROR(INDEX('FY 16 P&L'!$C$6:$O$61,MATCH(' MoM P&L FY16-FY18'!$B6,'FY 16 P&L'!$B$6:$B$61,0),MATCH(' MoM P&L FY16-FY18'!C$4,'FY 16 P&L'!$C$5:$N$5,0)),""))))