Help on Xlookup

[Gcsingh90]

Data is as follows

	Person 1	Person 2	Person 3	Person 4	Person 5	Person 6	Person 7	Person 8
1​	2​	3​	4​	5​	6​	7​	8​	9​
Jan-23​	2​	2​	3​	5​	6​	6​	2​	2​
Feb-23​	3​	7​	2​	4​	7​	3​	3​	3​
Mar-23​	3​	3​	3​	4​	2​	7​	6​	3​
Total	8​	12​	8​	13​	15​	16​	11​	8​
Apr-23​	2​	2​	5​	2​	6​	4​	5​	7​
May-23​	1​	2​	7​	6​	2​	5​	4​	7​
Jun-23​	6​	5​	2​	6​	7​	7​	1​	6​

Required Output

	Person 5	Person 6	Person 7	Person 8	Person 2	Person 1
Jan-23	6	6	2	2	2	2
Feb-23	7	3	3	3	7	3
Mar-23
Apr-23
May-23
Jun-23

Basically Output required is searching the Month and Person name and fetches the data from main data. I tried Xlookup and willing to understand different ways to acheive this. But Xlookup is primarly trying to understand. I tried using the formula below which gives error.

=XLOOKUP(D12,D1:L5,XLOOKUP(E11,E1:L1,E2:L5))

Please guide. Thanks

 

[Gcsingh90]

In the output table person's name can shuffle so how can a formula search the person in main data and return the value corresponding to Jan or any month? xlookup within xlookup?

 

[SunnyAlv]

try with index match

=INDEX($B$3:$I$9,MATCH($A14,$A$3:$A$9,0),MATCH(B$13,$B$1:$I$1,0))

 

[SunnyAlv]

try with index match

=INDEX($B$3:$I$9,MATCH($A14,$A$3:$A$9,0),MATCH(B$13,$B$1:$I$1,0))

View attachment 106967

if u want to using XLOOKUP try

paste it like in cell B14 as drawn

=XLOOKUP($A14,$A$3:$A$9,XLOOKUP(B$13,$B$1:$I$1,$B$3:$I$9))

 

[Peter_SSs]

willing to understand different ways to acheive this.

24 02 16.xlsm
	D	E	F	G	H	I	J	K	L
1		Person 1	Person 2	Person 3	Person 4	Person 5	Person 6	Person 7	Person 8
2	1	2	3	4	5	6	7	8	9
3	Jan-23	2	2	3	5	6	6	2	2
4	Feb-23	3	7	2	4	7	3	3	3
5	Mar-23	3	3	3	4	2	7	6	3
6	Total	8	12	8	13	15	16	11	8
7	Apr-23	2	2	5	2	6	4	5	7
8	May-23	1	2	7	6	2	5	4	7
9	Jun-23	6	5	2	6	7	7	1	6
10
11		Person 5	Person 6	Person 7	Person 8	Person 2	Person 1
12	Jan-23	6	6	2	2	2	2
13	Feb-23	7	3	3	3	7	3
14	Mar-23	2	7	6	3	3	3
15	Apr-23	6	4	5	7	2	2
16	May-23	2	5	4	7	2	1
17	Jun-23	7	7	1	6	5	6
Lookup

Cell Formulas
Range	Formula
E12:J17	E12	=LET(f,FILTER($E$3:$L$9,$D$3:$D$9=$D12),FILTER(f,$E$1:$L$1=E$11))

 

[Alex Blakenburg]

Most of the above will work for you but it sounds like you are specifically trying to understand why your XLookup doesn't work.
If that is the case see if this helps.
(Formula in E12)
The inner XLookup selects the column to return. (which column out of E to L)
The outer XLookup selects the row to return (which row out of 2 to 9)