OFFSET INDEX MATCH Across Multiple Sheets

[wlung]

Hi guys, first time poster here hoping to get a bit of help on a problem. I have a workbook with four sheets similar to this format:

Sheet1
SampleA1
SampleA2
SampleB1
SampleB2
SampleC1
SampleC2


Sheet2
ID Date
A1 Date1
A2 Date2

Sheet3
ID Date
B1 Date3
B2 Date4

Sheet4
ID Date
C1 Date5
C2 Date6

Essentially, Sheet1 is a master list, where I am trying to get SampleA1 to reference Date1, SampleB1 to reference Date3, and so on.

Here is what I have tried for the "SampleA1" cell:

=OFFSET(INDEX(Sheet2:Sheet4!$A$1:$A$3,MATCH("A1",Sheet2:Sheet4!$A$1:$A$3,0)),-1,0)

This keeps returning a #VALUE ! error. Does anyone know how to get around this? Is it even possible to use INDEX MATCH across multiple sheets like I've tried? Any help is much appreciated.

 

[copypastespecialvalues]

Hey, is your look up value in the other sheets?

 

[wlung]

Hi thanks for the reply. My look up value is the ID column in the other sheets.

 

[copypastespecialvalues]

Are you able to post a couple of rows from the master and look up tables?

 

[wlung]

Here is an example of what I'm trying to do

 

[Fluff]

Have a look here https://www.mrexcel.com/forum/excel...match-across-multiple-sheets.html#post4444271