Help with lookup

[Cpanc]

I am unable to find a solution to this issue. We have a file where we have multiple header columns and it is dynamic in a sense. Every month we can have new rows and new columns to the dataset. As seen in example photo below I am trying to lookup number for specific cell by row column headers.
Thank you all!

 

[Peter_SSs]

I have another follow up question though. Some of headers in the file are merged. For example AB Fee is merged from B to E.

Could we have a small set of dummy sample data in the new layout with XL2BB and explain again in relation to that sample data?
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[Peter_SSs]

Could we have a small set of dummy sample data in the new layout with XL2BB and explain again in relation to that sample data?

.. unless this is what you mean/want?

Cpanc.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		AB FEE				CD FEE
3		Level 1	Level 1	Level 2	Level 2	Level 1	Level 1	Level 2	Level 2
4		Class A	Class B	Class A	Class B	Class C	Class D	Class C	Class D
5	31/01/2024	1	10	20	30	40	50	60	70		AB Fee	CD Fee
6	29/02/2024	2	11	21	31	41	51	61	71		Level 2	Level 1
7	31/03/2024	3	12	22	32	42	52	62	72		Class A	Class D
8	30/04/2024	4	13	23	33	43	53	63	73		31/03/2024	31/07/2024
9	31/05/2024	5	14	24	34	44	54	64	74		22	56
10	30/06/2024	6	15	25	35	45	55	65	75
11	31/07/2024	7	16	26	36	46	56	66	76
12	31/08/2024	8	17	27	37	47	57	67	77
13	30/09/2024	9	18	28	38	48	58	68	78
14	31/10/2024	10	19	29	39	49	59	69	79
15	30/11/2024	11	20	30	40	50	60	70	80
16	31/12/2024	12	21	31	41	51	61	71	81
Sheet1

Cell Formulas
Range	Formula
K9:L9	K9	=INDEX($B$5:$I$16,MATCH(K8,$A$5:$A$16,0),MATCH(TEXTJOIN("|",,K5:K7),MAKEARRAY(1,8,LAMBDA(r,c,TEXTJOIN("|",,LOOKUP("zz",B2:INDEX($B$2:$I$2,c)),INDEX($B$3:$I$4,0,c)))),0))

 

[Cpanc]

.. unless this is what you mean/want?

Cpanc.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1
2		AB FEE				CD FEE
3		Level 1	Level 1	Level 2	Level 2	Level 1	Level 1	Level 2	Level 2
4		Class A	Class B	Class A	Class B	Class C	Class D	Class C	Class D
5	31/01/2024	1	10	20	30	40	50	60	70		AB Fee	CD Fee
6	29/02/2024	2	11	21	31	41	51	61	71		Level 2	Level 1
7	31/03/2024	3	12	22	32	42	52	62	72		Class A	Class D
8	30/04/2024	4	13	23	33	43	53	63	73		31/03/2024	31/07/2024
9	31/05/2024	5	14	24	34	44	54	64	74		22	56
10	30/06/2024	6	15	25	35	45	55	65	75
11	31/07/2024	7	16	26	36	46	56	66	76
12	31/08/2024	8	17	27	37	47	57	67	77
13	30/09/2024	9	18	28	38	48	58	68	78
14	31/10/2024	10	19	29	39	49	59	69	79
15	30/11/2024	11	20	30	40	50	60	70	80
16	31/12/2024	12	21	31	41	51	61	71	81
Sheet1

Cell Formulas
Range	Formula
K9:L9	K9	=INDEX($B$5:$I$16,MATCH(K8,$A$5:$A$16,0),MATCH(TEXTJOIN("|",,K5:K7),MAKEARRAY(1,8,LAMBDA(r,c,TEXTJOIN("|",,LOOKUP("zz",B2:INDEX($B$2:$I$2,c)),INDEX($B$3:$I$4,0,c)))),0))

Yea that is what I meant exactly. Will try the solution today. Thanks a ton

 

[Cpanc]

Yea that is what I meant exactly. Will try the solution today. Thanks a ton

The textjoin here has to be defined cell specific? Because data bas inconsistent merged cells. Some are merged for 10 columns while some are 5. Apologies should have given proper data set. Will try to provide one today. Thank you