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!!

Team A		Team B		Team C		Team D
Training 1	Level 3	Training 1​ <strike></strike>	Level 3	Training Session 1​ <strike></strike>	Level 3	Training Session 1​ <strike></strike>	Level 3
Training 2	Level 3	Training 2	Level 3​ <strike></strike>	Training 2​	Level 3​ <strike></strike>	Training 2​ <strike></strike>	<strike></strike> Level 3​ <strike></strike>	<strike></strike>
Training 3	Level 3			Training 3​ <strike></strike>	<strike></strike> Level 3​ <strike></strike>	<strike></strike>
						Training 4​	<strike></strike> Level 3​ <strike></strike>	<strike></strike>
Training 5​ <strike></strike>	Level 2	Training 5​ <strike></strike>	<strike></strike> Level 2​ <strike></strike>	Training 5​ <strike></strike>	<strike></strike> Level 2​ <strike></strike>	Training 5​ <strike></strike>	<strike></strike> Level 2​ <strike></strike>	<strike></strike>
Training 6​	<strike></strike> Level 2​ <strike></strike>	<strike></strike>		Training 6​ <strike></strike>	<strike></strike> Level 2​ <strike></strike>	Training 6​ <strike></strike>	<strike></strike> Level 2​ <strike></strike>	<strike></strike>
Training 7​	<strike></strike><strike></strike> Level 1​	<strike></strike> Training 7​ <strike></strike>	<strike></strike> Level 1​ <strike></strike>	<strike></strike>		Training 7​ <strike></strike>	Level 1​ <strike></strike>	<strike></strike>
		Training 8​	<strike></strike> Level 1​ <strike></strike>	<strike></strike>
		Training 9​	<strike></strike> Level 1​ <strike></strike>	<strike></strike>

<tbody>
</tbody>

 

[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