Complex lookup with two criteria

[rmcp20]

Hi all,

Need some help on a lookup formula that can populate results in a table based on two criteria.

Vlookup is useless, as i need two validations, and i have been trying INDEX & MATCH but without sucess.

I need the results in sheet "Data" - Column F (Laps) to be populated in sheet "Results" - column C and E (each individualy, i can use the same formula for both).

I would need excell to find the car number & Tyre brand and populate the results.

Example: Car 1 (A2) and Hankook (D1) in sheet "Results" being the criteria, i would need excell to populate the value in F3 from sheet "Data" in E1 on the "Results", as these is the one that match both criteria.


Data sheet

Car	Driver	Model	Dry tyre	Wet tyre	Laps
1	John	Pontiac	Goodyear		10
1	John	Pontiac		Hankook	7
3	Roy	Chevy	Hankook		11
4	Joseph	Toyota	Goodyear		13

<tbody>
</tbody>

Results sheet

Car	Goodyear	Laps	Hankook	Laps
1
2
3
4
5

<tbody>
</tbody>

Thanks a lot in advance.
Cheers,
Rui

 

[Aladin Akyurek]

Try to reword your objective. Why does the Results sheet Laps columns?

 

[rmcp20]

The objective is to populate the Laps for each tyre in the respective column for each car.

If car 1 does 10 laps with Goodyear, i want this value to be populated in 3rd column / 2nd row on the results table

 

[Aladin Akyurek]

The objective is to populate the Laps for each tyre in the respective column for each car.

If car 1 does 10 laps with Goodyear, i want this value to be populated in 3rd column / 2nd row on the results table

And what does go in the Goodyear column?

 

[gaz_chops]

Try these, amend the formulas to ref your sheet names etc -
In Results C1, enter with Ctrl,Shift & Enter
=IFERROR(IFERROR(INDEX($F$2:$F$5,MATCH($A12&$B$11,$A$2:$A$5&$D$2:$D$5,0)),INDEX($F$2:$F$5,MATCH($A12&$B$11,$A$2:$A$5&$E$2:$E$5,0))),"")
In Results F1, enter with Ctrl,Shift & Enter

=IFERROR(IFERROR(INDEX($F$2:$F$5,MATCH($A12&$D$11,$A$2:$A$5&$D$2:$D$5,0)),INDEX($F$2:$F$5,MATCH($A12&$D$11,$A$2:$A$5&$E$2:$E$5,0))),"")

This will give following results

[TABLE="width: 325"]
<tbody>[TR]
[TD="width: 65"]Car[/TD]
[TD="width: 65"]Goodyear[/TD]
[TD="width: 65"]Laps[/TD]
[TD="width: 65"]Hankook[/TD]
[TD="width: 65"]Laps[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]11[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

Or prob better, again Ctrl,Shift & Enter
C1
=IFERROR(INDEX($F$2:$F$8,IFERROR(MATCH($A12&$B$11,$A$2:$A$8&$D$2:$D$8,0),MATCH($A12&$B$11,$A$2:$A$8&$E$2:$E$8,0))),"")
F1
=IFERROR(INDEX($F$2:$F$8,IFERROR(MATCH($A12&$D$11,$A$2:$A$8&$D$2:$D$8,0),MATCH($A12&$D$11,$A$2:$A$8&$E$2:$E$8,0))),"")

 

[rmcp20]

And what does go in the Goodyear column?

Nothing for now, it will be a conditional formating afterwards...

 

[rmcp20]

Awesome, works!!!

The only was i had to populated these results was by using concatenate+vlookups, but to minimize human intervention i had to use a simple macro to make some adjustments on the tables, so that the vlookup would work.

This solves the problem. Thanks al lot!

 

[gaz_chops]

You're welcome, glad it helped.

 

[Snakehips]

Appreciate you are sorted but another possibility....

Excel 2007
	A	B	C	D	E
1	Car	Goodyear	Laps	Hankook	Laps
2	1		10		7
3	2
4	3				11
5	4		13
6	5
Result

Cell Formulas
Range	Formula
C2	=SUMPRODUCT((Data!$A$2:$A$20=$A2)*((Data!$D$2:$D$20=$B$1)+(Data!$E$2:$E$20=$B$1))*(Data!$F$2:$F$20))
E2	=SUMPRODUCT((Data!$A$2:$A$20=$A2)*((Data!$D$2:$D$20=$D$1)+(Data!$E$2:$E$20=$D$1))*(Data!$F$2:$F$20))

Returns 0 values if no laps but they can be 'turned off' via Excel Options