Can you use vlookup for this and if so how

[answer88]

I have one spreadsheet that looks something like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]track[/TD]
[TD]race[/TD]
[TD]n1[/TD]
[TD]n2
[/TD]
[TD]n3
[/TD]
[TD]n4
[/TD]
[TD]n5[/TD]
[TD]n6[/TD]
[/TR]
[TR]
[TD]1/11/2017
[/TD]
[TD]doomben
[/TD]
[TD]1
[/TD]
[TD]4.5
[/TD]
[TD][/TD]
[TD]2.3
[/TD]
[TD]18
[/TD]
[TD]8
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]1/11/2017
[/TD]
[TD]dubbo
[/TD]
[TD]5
[/TD]
[TD]2.3
[/TD]
[TD]4.5
[/TD]
[TD][/TD]
[TD]8
[/TD]
[TD]20
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/11/2017
[/TD]
[TD]flemington
[/TD]
[TD]4
[/TD]
[TD]2
[/TD]
[TD]5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]7
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD]track
[/TD]
[TD]race
[/TD]
[TD]date
[/TD]
[TD]n
[/TD]
[TD]Data go in this column
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]doomben
[/TD]
[TD]1
[/TD]
[TD]1/11/2017
[/TD]
[TD]n3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]doomben
[/TD]
[TD]1
[/TD]
[TD]1/11/2017
[/TD]
[TD]n1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]doomben
[/TD]
[TD]1
[/TD]
[TD]1/11/2017
[/TD]
[TD]n2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So it finishes looking like this

[TABLE="width: 500"]
<tbody>[TR]
[TD]track
[/TD]
[TD]race
[/TD]
[TD]date
[/TD]
[TD]n
[/TD]
[TD]data
[/TD]
[/TR]
[TR]
[TD]doomben
[/TD]
[TD]1
[/TD]
[TD]1/11/2017[/TD]
[TD]n3
[/TD]
[TD]2.3
[/TD]
[/TR]
[TR]
[TD]doomben
[/TD]
[TD]1
[/TD]
[TD]1/11/2017
[/TD]
[TD]n1
[/TD]
[TD]4.5
[/TD]
[/TR]
[TR]
[TD]doomben
[/TD]
[TD]1
[/TD]
[TD]1/11/2017
[/TD]
[TD]n2
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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[TABLE="class: grid, width: 400"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]date[/TD]
[TD]track[/TD]
[TD]race[/TD]
[TD]n1[/TD]
[TD]n2[/TD]
[TD]n3[/TD]
[TD]n4[/TD]
[TD]n5[/TD]
[TD]n6[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: right"]1/11/2017[/TD]
[TD]doomben[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2.3[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: right"]1/11/2017[/TD]
[TD]dubbo[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2.3[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"]2/11/2017[/TD]
[TD]flemington[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[/TR]
</tbody>[/TABLE]

Sheet4​

Excel 2010[TABLE="class: grid, width: 400"]
<colgroup><col><col><col><col><col><col></colgroup><tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]track[/TD]
[TD]race[/TD]
[TD]date[/TD]
[TD]n[/TD]
[TD]Data go in this column[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]doomben[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/11/2017[/TD]
[TD]n3[/TD]
[TD="align: right"]2.3[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]doomben[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/11/2017[/TD]
[TD]n1[/TD]
[TD="align: right"]4.5[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]doomben[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1/11/2017[/TD]
[TD]n2[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet5​

[TABLE="class: grid, width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="class: grid, width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TD="width: 10"]Cell[/TD]
[TD="align: left"]Formula[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E2[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E3[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E4[/TH]
[TD="align: left"]{=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)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]