Multiple lookup across rows. columns and sheets.

[shina67]

Hi All,

I am wanting to lookup the number in a column on the Data Sheet based on criteria from the Dashboard Sheet.

So in the attached pictures 'Dashboard' B2 = Thursday 04 January 2024 & F2 = Adeco. Based on these 2 criteria i want to return to the value from the 'Data' sheet column 'holidays'.

The cell references in 'Dashboard' B2 & F2 are dynamic.

Hope this makes sense. I have tried various different ways but have had no joy.

I am trying to broaden my horizons with formulas if this is possible using only formula.

 

[shina67]

I have 16 different headers as in the Data Sheet picture attached above.

 

[smozgur]

Please avoid marking a post that doesn't provide a solution. Doing so would give the impression that your question has been answered, and reduce the chance of receiving a helpful response from other members.

Therefore, I removed the solution mark from this and other question of yours.

 

[smozgur]

Maybe something like this.
Notes: Select the range in the data sheet and name it as "data" for easier application. I recommend applying the changes on a backup copy of the workbook.
The parameters and the formula is in the O2 cell. You need to implement it in your dashboard worksheet.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Date	Adeco					Bifolds						Date	Department	Holidays
2	Date	Sickness	Holidays	UA	Lates	AA	Sickness	Holidays	UA	Lates	AA		Friday, January 5, 2024	Bifolds	80
3	Tuesday, January 2, 2024	77	62	92	39	25	99	33	60	80	93
4	Wednesday, January 3, 2024	6	19	10	87	80	0	2	5	80	54
5	Thursday, January 4, 2024	57	5	61	79	43	19	4	57	81	38
6	Friday, January 5, 2024	24	80	44	1	79	40	80	10	47	2
7	Saturday, January 6, 2024	6	10	63	53	47	31	68	75	91	79
8	Sunday, January 7, 2024	79	44	87	50	86	8	81	56	65	3
9	Monday, January 8, 2024	48	49	17	45	98	66	19	11	59	95
10	Tuesday, January 9, 2024	91	8	60	80	80	80	52	14	35	4
11	Wednesday, January 10, 2024	24	45	18	43	65	75	39	57	58	49
12	Thursday, January 11, 2024	46	67	30	11	51	24	74	57	61	26
13	Friday, January 12, 2024	45	4	26	54	57	67	40	23	12	94
14	Saturday, January 13, 2024	21	6	52	69	43	62	42	94	79	40
15	Sunday, January 14, 2024	87	97	75	43	13	23	31	33	54	74
16	Monday, January 15, 2024	25	80	50	17	35	41	28	48	30	85
17	Tuesday, January 16, 2024	37	59	46	83	72	1	82	91	91	27
18	Wednesday, January 17, 2024	9	67	56	47	70	21	32	84	46	52
19	Thursday, January 18, 2024	3	49	3	49	51	63	48	26	53	76
20	Friday, January 19, 2024	7	5	82	27	78	23	15	39	31	23
21	Saturday, January 20, 2024	40	49	47	77	29	77	9	37	10	75
22	Sunday, January 21, 2024	92	45	72	76	26	40	65	74	4	87
Sheet1

Cell Formulas
Range	Formula
O2	O2	=INDEX(data,MATCH(M2,OFFSET(data,,,,1),0),QUOTIENT(MATCH(N2,OFFSET(data,,1,1,COLUMNS((data)-1)),0),5)*5+3)

Named Ranges
Name	Refers To	Cells
data	=Sheet1!$A$1:$K$22	O2

 

[shina67]

Hi smozgur,

My apologises about marking the thread as answered earlier. This is because I realised that I had posted a thread that wasn't what i actually required help with as I had solved the problem.

The problem that I do have is on a new thread -

xlookup, vlookup, hlookup with and/if multiple criteria. Which do I use?

Hi All, I have a problem with the below which I am hoping one of you genius people can help with. From image 1 the cell B2 is dynamic to any day of the year. Cell F2 is also a drop down which has 16 catergories. I need to be able to lookup in image 2 based on the 2 above criteria. As you can...

www.mrexcel.com