Using INDEX MATCH to reference multiple sheets

**myshadeofglory** · 10-19-2011, 02:58 PM

Is it possible to use an OR function in an INDEX MATCH formula to find matches on two different sheets?

I'm using the following formula to INDEX MATCH info on sheet1 of "workbookA" and return a value to sheet1 of "workbookB."

=INDEX('C:\Production\[workbookA.xls]sheet1'!$F$1:$F$65536,MATCH(A5367,''C:\Production\[workbookA.xls]sheet1'!$G$1:$G$65536,0))

I'd like to use it to INDEX MATCH info on sheet1 and sheet2 of "workbook A". Basically, info gets put onto sheet1 of workbookA, then gets moved to sheet2 to archive it. However, we want to keep this info on workbookB indefinitely.

**martindwilson** · 10-19-2011, 03:31 PM

maybe ,do you mean sheets or workbooks?

**myshadeofglory** · 10-19-2011, 04:36 PM

I'd like to use it to INDEX MATCH info on sheet1 and sheet2 of "workbook A". Basically, info gets put onto sheet1 of workbookA, then gets moved to sheet2 to archive it. However, we want to keep this info on workbookB indefinitely.

**martindwilson** · 10-22-2011, 07:31 AM

this should work
=INDEX('C:\Production\[workbookA.xls]sheet1'!$F$1:$F$65536,iferror(MATCH(A5367,''C:\Production\[workbookA.xls]sheet1'!$G$1:$G$65536,0),MATCH(A5367,''C:\Production\[workbookA.xls]sheet2'!$G$1:$G$65536,0)))