Complex Index Match

[thedeadzeds]

HI GUYS

I HAVE 2 TABS. ONE TAB CALLED SUMMARY AND ONE CALLED DATA

IS THERE A WAY TO DO THE FOLLOWING:

IF THERE IS A NAME IN B1 IN THE SUMMARY TAB THEN LOOK AT COLUMN A IN THE DATA TAB, LOOK AT THE DATE IN ROW 2 IN THE SUMMARY TAB AND BRING BACK THE VALUE IN THE RELEVANT ROW IN THE DATA TAB. SO IF FOR EXAMPLE I SELECTED CRAIG IN THE SUMMARY TAB ROW 3 WOULD SHOW AS PER THE SUMMARY TAB EXAMPLE BELOW


SUMMARY TAB

	A	B	C	D	E	F	G
1		Craig
2	Date	01/01/2017	02/01/2017	03/01/2017	04/01/2017	05/01/2017	06/01/2017
3	Result	S			H		B

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

DATA TAB

	A	B	C	D	E	F	G
1		Sun	Mon	Tue	Wed	Thu	Fri
2	Date	01/01/2017	02/01/2017	03/01/2017	04/01/2017	05/01/2017	06/01/2017
3	Craig	S			H		B
4	Ang		H	HS
5	Donna	H			S		B

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

 

[BarryL]

untested:

=IFERROR(INDEX($A$2:$G$5,MATCH($B$1,$A$2:$A$5,0),MATCH(B$2,$A$2:$G$2,0)),"")

 

[thedeadzeds]

Thanks Barry, the tables are in separate tabs so the Summary is in one tab and the data tab is in another tab

 

[BarryL]

Thanks Barry, the tables are in separate tabs so the Summary is in one tab and the data tab is in another tab

=IFERROR(INDEX(Data!$A$2:$G$5,MATCH(Summary!$B$1,Data!$A$2:$A$5,0),MATCH(Summary!B$2,Data!$A$2:$G$2,0)),"")

 

[thedeadzeds]

Perfect thank you very much

 

[BarryL]

You're welcome