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!

 

[hagia_sofia]

Hello, one way to address this could be:

=LET(
a,TOCOL(B5:I16),
b,TOCOL((A5:A16=K8)*(B2:I2=K5)*(B3:I3=K6)*(B4:I4=K7)),
FILTER(a,b=1))

 

[Jeffrey Mahoney]

Welcome. Once you get settled here, you'll find better tools to show your sheet data, like XL2BB. Get that quickly

You could add a helper row in row 1 and put a formula to merge those three headers.
=B2&B3&B4
copy that to the other cells over the headers and also to Cell K4.
Then you could use:

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

Cell Formulas
Range	Formula
B1:I1,K4	B1	=B2&" "&B3&" "&B4
L8	L8	=VLOOKUP(K8,A5:I16,MATCH(K4,B1:I1,0)+1,FALSE)
A6:A16	A6	=EOMONTH(A5,1)

 

[Cpanc]

Thank you for the responses. I will download the tool as well. Helper column is an option but very difficult. We get 50 of such tabs this weekly and they go to column FZ in most cases. Should have mentioned earlier but trying to set up something on a different sheet where everything just pulls in.

 

[Cpanc]

Hello, one way to address this could be:

=LET(
a,TOCOL(B5:I16),
b,TOCOL((A5:A16=K8)*(B2:I2=K5)*(B3:I3=K6)*(B4:I4=K7)),
FILTER(a,b=1))

Somehow getting a calc error. Will check on to it. Can this formula include blank rows and columns? Coz the sheet we get has many blank rows and columns in between

 

[Jeffrey Mahoney]

Disregard the helper row

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

Cell Formulas
Range	Formula
L8	L8	=VLOOKUP(K8,A5:I16,MATCH(K5&K6&K7,B2:I2&B3:I3&B4:I4,0)+1,FALSE)
A6:A16	A6	=EOMONTH(A5,1)

 

[Peter_SSs]

Welcome to the MrExcel board!

Based on that sample data, another option might be

=SUMPRODUCT((B2:I2=K5)*(B3:I3=K6)*(B4:I4=K7)*(A5:A16=K8),B5:I16)

 

[MARZIOTULLIO]

L8=SUMIF(A5:A16,K8,INDEX(B5:I16,,MATCH(K5&K6&K7,INDEX(B2:I2&B3:I3&B4:I4,0),0)))

 

[Cpanc]

Somehow getting a calc error. Will check on to it. Can this formula include blank rows and columns? Coz the sheet we get has many blank rows and columns in between

This works as well. Was an error on my end. Thank you

 

[Cpanc]

So thanks for all your help. All the solutions provided work. 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. Would there be a fix for this issue? Vlookup only picks up the first value in this case.

Thank you