Vlookup, Hlookup, Match, Index,... or probably a combination?

[conny_1]

Hello, ​

​

I'm looking for an Excel Formula and I hope that someone can help me out with this? ​

I tried to draw a simplified version of the Excel below.
I'm looking for a dynamic formula which returns the required training level for a specific Team.
For example:
- For Team C
- For Training 6
--> Which Level is required?

- For Team D
- For training 4
--> Which Level is required?

I don't want to replace the column-index-num each time (as the Excel is very big), the formula needs to be dynamic. ​

Thank you very much in advance!!


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Team A[/TD]
[TD][/TD]
[TD]Team B[/TD]
[TD][/TD]
[TD]Team C[/TD]
[TD][/TD]
[TD]Team D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Training 1[/TD]
[TD]Level 3[/TD]
[TD]

Training 1​

<strike></strike>[/TD]
[TD]Level 3[/TD]
[TD]

Training Session 1​

<strike></strike>[/TD]
[TD]Level 3[/TD]
[TD]

Training Session 1​

<strike></strike>[/TD]
[TD]Level 3[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Training 2[/TD]
[TD]Level 3[/TD]
[TD]Training 2[/TD]
[TD]

Level 3​

<strike></strike>[/TD]
[TD]

Training 2​

[/TD]
[TD]

Level 3​

<strike></strike>[/TD]
[TD]

Training 2​

<strike></strike>[/TD]
[TD]<strike></strike>

Level 3​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Training 3[/TD]
[TD]Level 3[/TD]
[TD][/TD]
[TD][/TD]
[TD]

Training 3​

<strike></strike>[/TD]
[TD]<strike></strike>

Level 3​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

Training 4​

[/TD]
[TD]<strike></strike>

Level 3​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Training 5​

<strike></strike>[/TD]
[TD]Level 2[/TD]
[TD]

Training 5​

<strike></strike>[/TD]
[TD]<strike></strike>

Level 2​

<strike></strike>[/TD]
[TD]

Training 5​

<strike></strike>[/TD]
[TD]<strike></strike>

Level 2​

<strike></strike>[/TD]
[TD]

Training 5​

<strike></strike>[/TD]
[TD]<strike></strike>

Level 2​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Training 6​

[/TD]
[TD]<strike></strike>

Level 2​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD]

Training 6​

<strike></strike>[/TD]
[TD]<strike></strike>

Level 2​

<strike></strike>[/TD]
[TD]

Training 6​

<strike></strike>[/TD]
[TD]<strike></strike>

Level 2​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

Training 7​

[/TD]
[TD]<strike></strike><strike></strike>

Level 1​

[/TD]
[TD]<strike></strike>

Training 7​

<strike></strike>[/TD]
[TD]<strike></strike>

Level 1​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD]

Training 7​

<strike></strike>[/TD]
[TD]

Level 1​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

Training 8​

[/TD]
[TD]<strike></strike>

Level 1​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]

Training 9​

[/TD]
[TD]<strike></strike>

Level 1​

<strike></strike>[/TD]
[TD]<strike></strike>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J
1	Team A		Team B		Team C		Team D			Team A
2	Training 1	Level 3	Training 1	Level 3	Training Session 1	Level 3	Training Session 1	Level 3		Training 2
3	Training 2	Level 3	Training 2	Level 3	Training 2	Level 3	Training 2	Level 3		Level 3
4	Training 3	Level 3	Training 5	Level 2	Training3	Level 3	Training 4	Level 3
5	Training 5	Level 2	Training 7	Level 1	Training 5	Level 2	Training 5	Level 2
6	Training 6	Level 2	Training 8	Level 1	Training 6	Level 2	Training 6	Level 2
7	Training 7	Level 1	Training 9	Level 1			Training 7	Level 1
Sheet1

Cell Formulas
Range	Formula
J3	=INDEX(INDEX($A:$H,,MATCH(J1,$A$1:$H$1,0)+1),MATCH(J2,INDEX($A:$H,,MATCH(J1,$A$1:$H$1,0)),0))

 

[conny_1]

Hello AlanY,

Thanks a lot, it worked!!
Perfect

 

[AlanY]

you're welcome