Look up to return value from first cell from left

[amolvijay]

Hi Experts
I am working on dataset where monthwise/quarterwise data is populated from master table. I need to create a summary table which will have values only from last month of the quarter...So summary table should look for quarter name in the main dataset and return values only from first left or last right column of that quarter..

Plz help with formula

	Q2	Q2	Q2	Q3	Q3	Q3	Q4	Q4	Q4
	JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR
SIG	20​	30​	40​	40​	45​	50​	45​	30​	20​
GBU	40​	40​	45​	50​	45​	30​	20​	10​	30​
DM	25​	30​	35​	40​	45​	50​	45​	30​	20​
GRF	30​	35​	40​	45​	50​	55​	60​	65​	70​
	Q2	Q3	Q4
SIG	40​	50​	20​
GBU	45​	30​	30​
DM	35​	50​	20​
GRF	40​	55​	70​

 

[Georgiboy]

Here is a couple of ways you could do it:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1		Q2	Q2	Q2	Q3	Q3	Q3	Q4	Q4	Q4
2		JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR
3	SIG	20	30	40	40	45	50	45	30	20
4	GBU	40	40	45	50	45	30	20	10	30
5	DM	25	30	35	40	45	50	45	30	20
6	GRF	30	35	40	45	50	55	60	65	70
7
8
9
10		Q2	Q3	Q4
11	SIG	40	50	20
12	GBU	45	30	30
13	DM	35	50	20
14	GRF	40	55	70
15
16
17		Q2	Q3	Q4
18	SIG	40	50	20
19	GBU	45	30	30
20	DM	35	50	20
21	GRF	40	55	70
Sheet1

Cell Formulas
Range	Formula
B11:D14	B11	=XLOOKUP($B$10:$D$10,$B$1:$J$1,B3:J3,,,-1)
B18:D21	B18	=TAKE(FILTER(B3:J6,B1:J1=B17),,-1)
Dynamic array formulas.

 

[amolvijay]

Here is a couple of ways you could do it:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I	J
1		Q2	Q2	Q2	Q3	Q3	Q3	Q4	Q4	Q4
2		JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR
3	SIG	20	30	40	40	45	50	45	30	20
4	GBU	40	40	45	50	45	30	20	10	30
5	DM	25	30	35	40	45	50	45	30	20
6	GRF	30	35	40	45	50	55	60	65	70
7
8
9
10		Q2	Q3	Q4
11	SIG	40	50	20
12	GBU	45	30	30
13	DM	35	50	20
14	GRF	40	55	70
15
16
17		Q2	Q3	Q4
18	SIG	40	50	20
19	GBU	45	30	30
20	DM	35	50	20
21	GRF	40	55	70
Sheet1

Cell Formulas
Range	Formula
B11:D14	B11	=XLOOKUP($B$10:$D$10,$B$1:$J$1,B3:J3,,,-1)
B18:D21	B18	=TAKE(FILTER(B3:J6,B1:J1=B17),,-1)
Dynamic array formulas.

Thank you Georgiboy.. it worked
However I am looking for two dimensional array where A11:A14 sequence wont be the same in larger dataset. The formula should look for the dept (SIG, GBU etc) and return quarterwise details in summary table

 

[Georgiboy]

Another option:

Book1
	A	B	C	D	E	F	G	H	I	J
1		Q2	Q2	Q2	Q3	Q3	Q3	Q4	Q4	Q4
2		JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR
3	SIG	20	30	40	40	45	50	45	30	20
4	GBU	40	40	45	50	45	30	20	10	30
5	DM	25	30	35	40	45	50	45	30	20
6	GRF	30	35	40	45	50	55	60	65	70
7
8
9
10		Q2	Q3	Q4
11	DM	35	50	20
12	GBU	45	30	30
13	SIG	40	50	20
14	GRF	40	55	70
Sheet1

Cell Formulas
Range	Formula
B11:D14	B11	=LET(tRng,A1:J6,c,A11:A14,r,B10:D10, q,TAKE(DROP(tRng,,1),1), data,CHOOSECOLS(tRng,1,BYCOL(r,LAMBDA(x,MAX(IF(q=x,COLUMN(q)))))), INDEX(data,MATCH(c,INDEX(data,,1),0),MATCH(r,INDEX(data,1,0))) )
Dynamic array formulas.

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1		Q2	Q2	Q2	Q3	Q3	Q3	Q4	Q4	Q4
2		JUL	AUG	SEP	OCT	NOV	DEC	JAN	FEB	MAR
3	SIG	20	30	40	40	45	50	45	30	20
4	GBU	40	40	45	50	45	30	20	10	30
5	DM	25	30	35	40	45	50	45	30	20
6	GRF	30	35	40	45	50	55	60	65	70
7
8
9		Q2	Q3	Q4
10	DM	35	50	20
11	GRF	40	55	70
12	SIG	40	50	20
13	GBU	45	30	30
Data

Cell Formulas
Range	Formula
B10:D13	B10	=XLOOKUP(B$9:D$9,$B$1:$J$1,FILTER($B$3:$J$6,$A$3:$A$6=A10),,,-1)
Dynamic array formulas.