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)),"")

Particulars	Sep-15	Oct-15	Nov-15
Total Income
Sales

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[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)),""))))