If cell matches data in a range of other cells show match

[SaraWitch]

Hello peeps,

I have a sheet for calculating working days between two dates (Calculator) and a sheet with year by year bank holidays ('UKBH').

I've put data validation in Calculator!C4 based on the years in UKBH!B2:I2. Start date is in Calculator!C6 and end date is in Calculator!C8.

I want the relevant year's holidays (ranging from 7 to 10 entries (rows beneath year)) to show in a separate column on UKBH! (K) when a user selects the year from Calculator!C4. Column K will then be part of my formula in Calculator!C8 '=IFERROR(IF($C6="","",IF($C8="",NETWORKDAYS($C6,TODAY()-1,UKBH!$K$3:$K$12),NETWORKDAYS($C6,$C8-1,UKBH!$K$3:$K$12))),"")'

I've tried a range of formulas from VLOOKUP to MATCH, but am now tying myself up in knots.

Any help would be appreciated

 

[awoohaw]

@SaraWitch
Would you be kind enough to help the forum help you by posting your data and expected results?
Mr. Excel has a tool called xl2bb add in that allows you to post mini worksheets into posts so the forum does not need to recreate your scenario.
If you cannot use xl2bb then please post your data tables.

The reason is that it takes time to recreate your scenario and there could be errors.

Thank in advance.

 

[SaraWitch]

Of course, sorry.

Calculation sheet and I've noticed my calculation formula (C10) doesn't work after putting in the formulas in UKBH!K3:K12!:

Void Loss Working Days Calculator.xlsx
	B	C
2	Void Loss - Working Days
3
4	Enter year:	2022/2023
5
6	Enter TTD:	20/07/2023
7
8	Enter TSD:	21/07/2023
9
10	Working days:
Calculator

Cell Formulas
Range	Formula
C10	C10	=IFERROR(IF($C6="","",IF($C8="",NETWORKDAYS($C6,TODAY()-1,UKBH!$K$3:$K$12),NETWORKDAYS($C6,$C8-1,UKBH!$K$3:$K$12))),"")

Cells with Data Validation
Cell	Allow	Criteria
C6	Date	between 01/01/1900 and 31/12/3000
C8	Date	between 01/01/1900 and 31/12/3000
C4	List	=UKBH!$B$2:$I$2

UKBH sheet (excuse my rambling formulas at the end!):

Void Loss Working Days Calculator.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M
1	UK Bank Holidays
2	2022/2023	2023/2024	2024/2025	2025/2026	2026/2027	2027/2028	2028/2029	2029/2030		2022/2023	=IF(COUNTIF($B2:$I2,Calculator!$C$4,B2:I2,"")	2022/2023
3	15/04/2022	07/04/2023	01/04/2024	18/04/2025	03/04/2026	29/04/2027	14/04/2028	02/04/2029		15/04/2022	#N/A	02/04/2029
4	18/04/2022	10/04/2023	06/05/2024	21/04/2025	06/04/2026	03/05/2027	17/04/2028	07/05/2029		18/04/2022	07/05/2029	07/05/2029
5	02/05/2022	01/05/2023	27/05/2024	05/05/2025	04/05/2026	31/05/2027	01/05/2028	28/05/2029		02/05/2022	28/05/2029	28/05/2029
6	02/06/2022	08/05/2023	26/08/2024	26/05/2025	25/05/2026	30/08/2027	29/05/2028	27/08/2029		02/06/2022	27/08/2029	27/08/2029
7	03/06/2022	29/08/2023	25/12/2024	25/08/2025	31/08/2026	27/12/2027	28/08/2028	25/12/2029		03/06/2022	25/12/2029	25/12/2029
8	29/08/2022	28/08/2023	26/12/2024	25/12/2025	25/12/2026	28/12/2027	25/12/2028	26/12/2029		29/08/2022	26/12/2029	26/12/2029
9	26/12/2022	25/12/2023	01/01/2025	26/12/2025	28/12/2026	03/01/2028	26/12/2028	01/01/1930		26/12/2022	01/01/1930	01/01/1930
10	27/12/2022	26/12/2023		01/01/2026	01/01/2027		01/01/2029			27/12/2022
11	02/01/2023	01/01/2024			26/03/2027		30/03/2029			02/01/2023
12		29/03/2024
UKBH

Cell Formulas
Range	Formula
M2:T2	M2	=IF(COUNTIF(B2:I2,Calculator!$C$4)>0,B2:I2)
L3	L3	=HLOOKUP(L2,B2:I12,10,0)
K3	K3	=IF(HLOOKUP($K$2,$B$2:$I$12,2,0)="","",HLOOKUP($K$2,$B$2:$I$12,2,0))
K4	K4	=IF(HLOOKUP($K$2,$B$2:$I$12,3,0)="","",HLOOKUP($K$2,$B$2:$I$12,3,0))
K5	K5	=IF(HLOOKUP($K$2,$B$2:$I$12,4,0)="","",HLOOKUP($K$2,$B$2:$I$12,4,0))
K6	K6	=IF(HLOOKUP($K$2,$B$2:$I$12,5,0)="","",HLOOKUP($K$2,$B$2:$I$12,5,0))
K7	K7	=IF(HLOOKUP($K$2,$B$2:$I$12,6,0)="","",HLOOKUP($K$2,$B$2:$I$12,6,0))
K8	K8	=IF(HLOOKUP($K$2,$B$2:$I$12,7,0)="","",HLOOKUP($K$2,$B$2:$I$12,7,0))
K9	K9	=IF(HLOOKUP($K$2,$B$2:$I$12,8,0)="","",HLOOKUP($K$2,$B$2:$I$12,8,0))
K10	K10	=IF(HLOOKUP($K$2,$B$2:$I$12,9,0)="","",HLOOKUP($K$2,$B$2:$I$12,9,0))
K11	K11	=IF(HLOOKUP($K$2,$B$2:$I$12,10,0)="","",HLOOKUP($K$2,$B$2:$I$12,10,0))
K12	K12	=IF(HLOOKUP($K$2,$B$2:$I$12,11,0)="","",HLOOKUP($K$2,$B$2:$I$12,11,0))
Dynamic array formulas.

 

[SaraWitch]

I've just had a thought that I may be overcomplicating things - is there a formula to use in Calculation!C10 that if Calculation!C4 is a particular year, then use corresponding holidays on UKBH!...?

Or add the bank holidays up and subtract the corresponding year from the Calculation!C10 formula...? But I still can't get my head around what that formula would be!

Void Loss Working Days Calculator.xlsx
	B	C	D	E	F	G	H	I	J	K
1	UK Bank Holidays
2	2022/2023	2023/2024	2024/2025	2025/2026	2026/2027	2027/2028	2028/2029	2029/2030		2022/2023
3	15/04/2022	07/04/2023	01/04/2024	18/04/2025	03/04/2026	29/04/2027	14/04/2028	02/04/2029		15/04/2022
4	18/04/2022	10/04/2023	06/05/2024	21/04/2025	06/04/2026	03/05/2027	17/04/2028	07/05/2029		18/04/2022
5	02/05/2022	01/05/2023	27/05/2024	05/05/2025	04/05/2026	31/05/2027	01/05/2028	28/05/2029		02/05/2022
6	02/06/2022	08/05/2023	26/08/2024	26/05/2025	25/05/2026	30/08/2027	29/05/2028	27/08/2029		02/06/2022
7	03/06/2022	29/08/2023	25/12/2024	25/08/2025	31/08/2026	27/12/2027	28/08/2028	25/12/2029		03/06/2022
8	29/08/2022	28/08/2023	26/12/2024	25/12/2025	25/12/2026	28/12/2027	25/12/2028	26/12/2029		29/08/2022
9	26/12/2022	25/12/2023	01/01/2025	26/12/2025	28/12/2026	03/01/2028	26/12/2028	01/01/1930		26/12/2022
10	27/12/2022	26/12/2023		01/01/2026	01/01/2027		01/01/2029			27/12/2022
11	02/01/2023	01/01/2024			26/03/2027		30/03/2029			02/01/2023
12		29/03/2024
13
14	9	10	7	8	9	7	9	7
15
UKBH

Cell Formulas
Range	Formula
K3	K3	=IF(HLOOKUP($K$2,$B$2:$I$12,2,0)="","",HLOOKUP($K$2,$B$2:$I$12,2,0))
K4	K4	=IF(HLOOKUP($K$2,$B$2:$I$12,3,0)="","",HLOOKUP($K$2,$B$2:$I$12,3,0))
K5	K5	=IF(HLOOKUP($K$2,$B$2:$I$12,4,0)="","",HLOOKUP($K$2,$B$2:$I$12,4,0))
K6	K6	=IF(HLOOKUP($K$2,$B$2:$I$12,5,0)="","",HLOOKUP($K$2,$B$2:$I$12,5,0))
K7	K7	=IF(HLOOKUP($K$2,$B$2:$I$12,6,0)="","",HLOOKUP($K$2,$B$2:$I$12,6,0))
K8	K8	=IF(HLOOKUP($K$2,$B$2:$I$12,7,0)="","",HLOOKUP($K$2,$B$2:$I$12,7,0))
K9	K9	=IF(HLOOKUP($K$2,$B$2:$I$12,8,0)="","",HLOOKUP($K$2,$B$2:$I$12,8,0))
K10	K10	=IF(HLOOKUP($K$2,$B$2:$I$12,9,0)="","",HLOOKUP($K$2,$B$2:$I$12,9,0))
K11	K11	=IF(HLOOKUP($K$2,$B$2:$I$12,10,0)="","",HLOOKUP($K$2,$B$2:$I$12,10,0))
K12	K12	=IF(HLOOKUP($K$2,$B$2:$I$12,11,0)="","",HLOOKUP($K$2,$B$2:$I$12,11,0))
B14:I14	B14	=COUNT(B3:B12)

 

[awoohaw]

I really am not sure why you need to segregate your holidays by year for this process.
The NETWORKDAYS.INTL and WORKDAYS.INTL functions can have an entire column of holidays if it wants.
If you are choosing the begin and start dates on the Calculator Sheet you already have all the information you need without filtering in the UKHD sheet.

Or have I missed something?

 

[awoohaw]

So, this is my suggestion. I've stacked your holiday list and put it on the Calculation Sheet (for ease of development) as a named range.
Then I use the cell reference functionality of the INDEX function to build your holiday dates.
I've also added a networkdays calculation using Sat and Sun as weekends - if your days off are different this will need to be adjusted.
Here it is:

If you are using Excel 2010 you probably need to enter the holiday formula using the CNTL-SHFT-ENTR keystroke.

Mr excel questions 53.xlsm
	A	B	C	D	E	F
1
2		Void Loss - Working Days				HolidayList
3						2022-04-15
4		Enter year:	2022/2023			2022-04-18
5						2022-05-02
6		Enter TTD:	2022-07-20			2022-06-02
7						2022-06-03
8		Enter TSD:	2023-07-21			2022-08-29
9						2022-12-26
10		Working days:				2022-12-27
11						2023-01-02
12						2023-04-07
13		awoohaw's working days (Sat Sun Not worked)	255			2023-04-10
14		Awoohaw's holidays between the dates	2022-08-29			2023-05-01
15			2022-12-26			2023-05-08
16			2022-12-27			2023-08-29
17			2023-01-02			2023-08-28
18			2023-04-07			2023-12-25
19			2023-04-10			2023-12-26
20			2023-05-01			2024-01-01
21			2023-05-08			2024-03-29
22						2024-04-01
23						2024-05-06
24						2024-05-27
25						2024-08-26
26						2024-12-25
27						2024-12-26
28						2025-01-01
29						2025-04-18
30						2025-04-21
31						2025-05-05
32						2025-05-26
33						2025-08-25
34						2025-12-25
35						2025-12-26
36						2026-01-01
37						2026-04-03
38						2026-04-06
39						2026-05-04
40						2026-05-25
41						2026-08-31
42						2026-12-25
43						2026-12-28
44						2027-01-01
45						2027-03-26
46						2027-04-29
47						2027-05-03
48						2027-05-31
49						2027-08-30
50						2027-12-27
51						2027-12-28
52						2028-01-03
53						2028-04-14
54						2028-04-17
55						2028-05-01
56						2028-05-29
57						2028-08-28
58						2028-12-25
59						2028-12-26
60						2029-01-01
61						2029-03-30
62						2029-04-02
63						2029-05-07
64						2029-05-28
65						2029-08-27
66						2029-12-25
67						2029-12-26
68						1930-01-01
Calculator

Cell Formulas
Range	Formula
C10	C10	=IFERROR(IF($C6="","",IF($C8="",NETWORKDAYS($C6,TODAY()-1,UKBH!$K$3:$K$12),NETWORKDAYS($C6,$C8-1,UKBH!$K$3:$K$12))),"")
C13	C13	=NETWORKDAYS.INTL(C6,C8,1,HolidayList)
C14:C21	C14	=INDEX(HolidayList,MATCH($C$6,HolidayList,1)+1,1):INDEX(HolidayList,MATCH($C$8,HolidayList,1),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
HolidayList	=Calculator!$F$3:$F$68	C13:C21

 

[SaraWitch]

Oh my; I didn't know about that formula - how fabulous! Thank you @awoohaw!

However, whilst it works on the dates you used, it's not on mine. For instance working days minus BH between 07/05/2023 and 15/05/2023 should be 4 and this replicated formula is returning 5. My holiday dates also aren't returning. What am I doing wrong?

Void Loss Working Days Calculator.xlsx
	B	C	D	E	F	G	H	I	J
2	Void Loss - Working Days				Void Loss - Working Days			Void Loss - Working Days
3
4	Enter TTD:	20/07/2022			Enter TTD:	07/05/2023		Enter TTD:	07/05/2023
5
6	Enter TSD:	21/07/2023			Enter TSD:	15/05/2023		Enter TSD:	15/05/2023
7
8	Working days:	255			Working days:	5		Working days:	4
9
10
11		29/08/2022		15/04/2022		08/05/2023			07/04/2023
12		29/08/2022		18/04/2022		08/05/2023			10/04/2023
13		29/08/2022		02/05/2022		08/05/2023			01/05/2023
14		29/08/2022		02/06/2022		08/05/2023			08/05/2023
15		29/08/2022		03/06/2022		08/05/2023			29/08/2023
16		29/08/2022		29/08/2022		08/05/2023			28/08/2023
17		29/08/2022		26/12/2022		08/05/2023			25/12/2023
18		29/08/2022		27/12/2022		08/05/2023			26/12/2023
19		29/08/2022		02/01/2023		08/05/2023			01/01/2024
20		29/08/2022		07/04/2023		08/05/2023			29/03/2024
21		29/08/2022		10/04/2023		08/05/2023
22				01/05/2023
23				08/05/2023
24				29/08/2023
25				28/08/2023
26				25/12/2023
27				26/12/2023
28				01/01/2024
29				29/03/2024
30				01/04/2024
31				06/05/2024
32				27/05/2024
33				26/08/2024
34				25/12/2024
35				26/12/2024
36				01/01/2025
37				18/04/2025
38				21/04/2025
39				05/05/2025
40				26/05/2025
41				25/08/2025
42				25/12/2025
43				26/12/2025
44				01/01/2026
45				03/04/2026
46				06/04/2026
47				04/05/2026
48				25/05/2026
49				31/08/2026
50				25/12/2026
51				28/12/2026
52				01/01/2027
53				26/03/2027
54				29/04/2027
55				03/05/2027
56				31/05/2027
57				30/08/2027
58				27/12/2027
59				28/12/2027
60				03/01/2028
61				14/04/2028
62				17/04/2028
63				01/05/2028
64				29/05/2028
65				28/08/2028
66				25/12/2028
67				26/12/2028
68				01/01/2029
69				30/03/2029
70				02/04/2029
71				07/05/2029
72				28/05/2029
73				27/08/2029
74				25/12/2029
75				26/12/2029
76				01/01/2030
Calculator

Cell Formulas
Range	Formula
C8,G8	C8	=NETWORKDAYS.INTL(C4,C6,1,HolidayList)
J8	J8	=IFERROR(IF($J4="","",IF($J6="",NETWORKDAYS($J4,TODAY()-1,$J11:$J20),NETWORKDAYS($J4,$J6-1,$J11:$J20))),"")
C11:C21	C11	=INDEX(HolidayList,MATCH($C$4,HolidayList,1)+1,1):INDEX(HolidayList,MATCH($C$6,HolidayList,1),1)
G11:G21	G11	=INDEX(HolidayList,MATCH($J$4,HolidayList,1)+1,1):INDEX(HolidayList,MATCH($J$6,HolidayList,1),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
HolidayList	=Calculator!$E$11:$E$76	G8, C8, G11:G21, C11:C21

Cells with Data Validation
Cell	Allow	Criteria
C4	Date	between 01/01/1900 and 31/12/3000
C6	Date	between 01/01/1900 and 31/12/3000
J4	Date	between 01/01/1900 and 31/12/3000
J6	Date	between 01/01/1900 and 31/12/3000
G4	Date	between 01/01/1900 and 31/12/3000
G6	Date	between 01/01/1900 and 31/12/3000

 

[SaraWitch]

Ah, re the working days, I remembered I had to minus 1 day because of the way the formula calculates and the way we record the days; so, that's fine. It would just be handy to know what I'm doing wrong with the return holiday dates for that period though...

 

[awoohaw]

you're missing the weekend argument in cell J8,
But, i don't know why you want to create a holiday subset. But, if that is what you want to use it should work with the following update:

=IFERROR(IF($J4="","",IF($J6="",NETWORKDAYS($J4,TODAY()-1,    1   ,$J11:$J20),NETWORKDAYS($J4,$J6-1,   1    ,$J11:$J20))),"")

 

[awoohaw]

you're missing the weekend argument in cell J8,
But, i don't know why you want to create a holiday subset. But, if that is what you want to use it should work with the following update:

=IFERROR(IF($J4="","",IF($J6="",NETWORKDAYS($J4,TODAY()-1,    1   ,$J11:$J20),NETWORKDAYS($J4,$J6-1,   1    ,$J11:$J20))),"")

This is wrong, please ignore.