Xlookup help

[zinah]

Hi,

I have below table and what I need is to get the count based on selected team for each tenure, I was trying to use the xlookup but it's not working, as it's giving me #VALUE, can anyone help me with refining the formula to get the same as expected results column?

Xlookup help.xlsx
	A	B	C	D	E	F	G	H	I	J
1		Team1	Team2	Team3	Team4
2	Tenure	Count	Count	Count	Count
3	> 10 Yrs	7	61		173
4	5-10 Yrs	9	41		149
5	2-5 Yrs	15	39		282
6	1-2 Yrs	5	23		122
7	< 1 Yr	11	7		40
8	< 2 Months	NA	NA		8
9	< 6 Months	NA	8		NA
10							Team1
11								Expected results
12							Tenure	Count
13							> 10 Yrs	7	#VALUE!
14							5-10 Yrs	9
15							2-5 Yrs	15
16							1-2 Yrs	5
17							< 1 Yr	11
18							< 2 Months	NA
19							< 6 Months	NA
20
Sheet1

Cell Formulas
Range	Formula
I13	I13	=XLOOKUP(1,(B1:E1=G10)*(A3:A9=G13),B3:E9)

Cells with Data Validation
Cell	Allow	Criteria
G10	List	=$B$1:$E$1

 

[JamesCanale]

The array sizes don't match - so it will throw an error. Index Match might be better.

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I
1		Team1	Team2	Team3	Team4
2	Tenure	Count	Count	Count	Count
3	> 10 Yrs	7	61		173
4	5-10 Yrs	9	41		149
5	2-5 Yrs	15	39		282
6	1-2 Yrs	5	23		122
7	< 1 Yr	11	7		40
8	< 2 Months	NA	NA		8
9	< 6 Months	NA	8		NA
10							Team2
11								Expected results
12							Tenure	Count
13							> 10 Yrs	7	61
14							5-10 Yrs	9	41
15							2-5 Yrs	15	39
16							1-2 Yrs	5	23
17							< 1 Yr	11	7
18							< 2 Months	NA	NA
19							< 6 Months	NA	8
Sheet2

Cell Formulas
Range	Formula
I13:I19	I13	=INDEX($B$3:$E$9,XMATCH($G13,$A$3:$A$9,0),XMATCH($G$10,$B$1:$E$1,0))

 

[Cubist]

Another option.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1		Team1	Team2	Team3	Team4
2	Tenure	Count	Count	Count	Count
3	> 10 Yrs	7	61		173
4	5-10 Yrs	9	41		149
5	2-5 Yrs	15	39		282
6	1-2 Yrs	5	23		122
7	< 1 Yr	11	7		40
8	< 2 Months	NA	NA		8
9	< 6 Months	NA	8		NA
10							Team1
11								Expected results
12							Tenure	Count
13							> 10 Yrs	7	7
14							5-10 Yrs	9	9
15							2-5 Yrs	15	15
16							1-2 Yrs	5	5
17							< 1 Yr	11	11
18							< 2 Months	NA	NA
19							< 6 Months	NA	NA
Sheet6

Cell Formulas
Range	Formula
I13:I19	I13	=TOCOL(IFS(G13:G19&G10=A3:A9&B1:E1,B3:E9),2)
Dynamic array formulas.

 

[zinah]

The array sizes don't match - so it will throw an error. Index Match might be better.

MrExcelPlayground23.xlsx
	A	B	C	D	E	F	G	H	I
1		Team1	Team2	Team3	Team4
2	Tenure	Count	Count	Count	Count
3	> 10 Yrs	7	61		173
4	5-10 Yrs	9	41		149
5	2-5 Yrs	15	39		282
6	1-2 Yrs	5	23		122
7	< 1 Yr	11	7		40
8	< 2 Months	NA	NA		8
9	< 6 Months	NA	8		NA
10							Team2
11								Expected results
12							Tenure	Count
13							> 10 Yrs	7	61
14							5-10 Yrs	9	41
15							2-5 Yrs	15	39
16							1-2 Yrs	5	23
17							< 1 Yr	11	7
18							< 2 Months	NA	NA
19							< 6 Months	NA	8
Sheet2

Cell Formulas
Range	Formula
I13:I19	I13	=INDEX($B$3:$E$9,XMATCH($G13,$A$3:$A$9,0),XMATCH($G$10,$B$1:$E$1,0))

Thanks soooo much, it's my first time to get to know the XMATCH, I'll use your formula in other filed and the other suggested formula by @Cubist to familiarize myself more with xmatch!

 

[zinah]

Another option.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1		Team1	Team2	Team3	Team4
2	Tenure	Count	Count	Count	Count
3	> 10 Yrs	7	61		173
4	5-10 Yrs	9	41		149
5	2-5 Yrs	15	39		282
6	1-2 Yrs	5	23		122
7	< 1 Yr	11	7		40
8	< 2 Months	NA	NA		8
9	< 6 Months	NA	8		NA
10							Team1
11								Expected results
12							Tenure	Count
13							> 10 Yrs	7	7
14							5-10 Yrs	9	9
15							2-5 Yrs	15	15
16							1-2 Yrs	5	5
17							< 1 Yr	11	11
18							< 2 Months	NA	NA
19							< 6 Months	NA	NA
Sheet6

Cell Formulas
Range	Formula
I13:I19	I13	=TOCOL(IFS(G13:G19&G10=A3:A9&B1:E1,B3:E9),2)
Dynamic array formulas.

Thank you soooo much! Thanks for introducing me to TOCOL formula, I believe it's very helpful and straightforward!

 

[Cubist]

You're welcome.