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]

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...

Sorry that my last posts were misleading.

You're not using the NETWORKDAYS.INTL function in J8.

try this:

=NETWORKDAYS.INTL(J4,J6-1,1,J11:J20)

 

[SaraWitch]

No worries; I have the formula calculating the correct working days minus bank holidays, and I don't really need the bank holiday dates to show, but just wondered why I couldn't return the BH dates between the period like you (C11:C21 not G11:G21 as there's only one BH in that period)...?

Void Loss Working Days Calculator.xlsx
	B	C	D	E	F	G
2	Void Loss - Working Days				Void Loss - Working Days
3
4	Enter TTD:	20/07/2022			Enter TTD:	07/05/2023
5
6	Enter TSD:	21/07/2023			Enter TSD:	15/05/2023
7
8	Working days:	255			Working days:	4
9
10
11		29/08/2022		15/04/2022		08/05/2023
12		29/08/2022		18/04/2022		08/05/2023
13		29/08/2022		02/05/2022		08/05/2023
14		29/08/2022		02/06/2022		08/05/2023
15		29/08/2022		03/06/2022		08/05/2023
16		29/08/2022		29/08/2022		08/05/2023
17		29/08/2022		26/12/2022		08/05/2023
18		29/08/2022		27/12/2022		08/05/2023
19		29/08/2022		02/01/2023		08/05/2023
20		29/08/2022		07/04/2023		08/05/2023
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 (3)

Cell Formulas
Range	Formula
C8	C8	=NETWORKDAYS.INTL(C4,C6,1,HolidayList)
G8	G8	=IF(G4="","",NETWORKDAYS.INTL(G4,G6,1,HolidayList)-1)
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($G$4,HolidayList,1)+1,1):INDEX(HolidayList,MATCH($G$6,HolidayList,1),1)
Press CTRL+SHIFT+ENTER to enter array formulas.

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

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
G4	Date	between 01/01/1900 and 31/12/3000
G6	Date	between 01/01/1900 and 31/12/3000

 

[awoohaw]

well, I just looked at it.
There is only 1 holiday in the range specified.

When you enter that formula in the cell, you may need to enter it with the array enter keys: CNTL-SHFT-ENTR
And the result spills, you do not copy the formula down the column, it should spill.

Unless you're in Excel 2010 and it does not spill. (Please advise).

 

[SaraWitch]

Ah, of course! Apologies, that's my ignorance around array/spill formulas - and, yes, is now working perfectly! (I use Excel 365.)

Thank you so much for you help and patience, @awoohaw

 

[awoohaw]

Ah, of course! Apologies, that's my ignorance around array/spill formulas - and, yes, is now working perfectly! (I use Excel 365.)

Thank you so much for you help and patience, @awoohaw

I am pleased you found a solution at the Mr. Excel Forum. I enjoy excel challenges. There are some other folks here much more skilled at excel than me, and I have learned quite a bit here as well.

Best Wishes!