Can you use vlookup for this and if so how

[answer88]

I have one spreadsheet that looks something like this

date	track	race	n1	n2	n3	n4	n5	n6
1/11/2017	doomben	1	4.5		2.3	18	8	6
1/11/2017	dubbo	5	2.3	4.5		8	20
2/11/2017	flemington	4	2	5				7

<tbody>
</tbody>

now i want to get the correct n value for the correct track and date into a single spread sheet that looks similar to this

track	race	date	n	Data go in this column
doomben	1	1/11/2017	n3
doomben	1	1/11/2017	n1
doomben	1	1/11/2017	n2

<tbody>
</tbody>

So it finishes looking like this

track	race	date	n	data
doomben	1	1/11/2017	n3	2.3
doomben	1	1/11/2017	n1	4.5
doomben	1	1/11/2017	n2

<tbody>
</tbody>

Race can go up to 12. n can go up to 24.

is this possible to do.

ie i can make a new spreadsheet with that can match the track date race and n to give the correct data from my main sheet

Thanks so much for your time. Tried using a nested if function but is way to big and confusing

 

[Scott T]

Change ranges to match your data

Excel 2010

	A	B	C	D	E	F	G	H	I
1	date	track	race	n1	n2	n3	n4	n5	n6
2	1/11/2017	doomben	1	4.5		2.3	18	8	6
3	1/11/2017	dubbo	5	2.3	4.5		8	20
4	2/11/2017	flemington	4	2	5				7

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet4​

Excel 2010

	A	B	C	D	E
1	track	race	date	n	Data go in this column
2	doomben	1	1/11/2017	n3	2.3
3	doomben	1	1/11/2017	n1	4.5
4	doomben	1	1/11/2017	n2

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet5​

Array Formulas Cell Formula E2 {=IF(COUNTIFS(Sheet4!$B$2:$B$4,A2,Sheet4!$C$2:$C$4,B2,Sheet4!A2:A4,C2)=0,"",INDEX(Sheet4!$D$2:$I$4,MATCH(Sheet5!A2&Sheet5!B2&Sheet5!C2,Sheet4!$B$2:$B$4&Sheet4!$C$2:$C$4&Sheet4!$A$2:$A$4,0),MATCH(D2,Sheet4!$D$1:$I$1,0)))} E3 {=IF(COUNTIFS(Sheet4!$B$2:$B$4,A3,Sheet4!$C$2:$C$4,B3,Sheet4!A3:A5,C3)=0,"",INDEX(Sheet4!$D$2:$I$4,MATCH(Sheet5!A3&Sheet5!B3&Sheet5!C3,Sheet4!$B$2:$B$4&Sheet4!$C$2:$C$4&Sheet4!$A$2:$A$4,0),MATCH(D3,Sheet4!$D$1:$I$1,0)))} E4 {=IF(COUNTIFS(Sheet4!$B$2:$B$4,A4,Sheet4!$C$2:$C$4,B4,Sheet4!A4:A6,C4)=0,"",INDEX(Sheet4!$D$2:$I$4,MATCH(Sheet5!A4&Sheet5!B4&Sheet5!C4,Sheet4!$B$2:$B$4&Sheet4!$C$2:$C$4&Sheet4!$A$2:$A$4,0),MATCH(D4,Sheet4!$D$1:$I$1,0)))} <tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>