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

SaraWitch

SaraWitch

Active Member
Joined
Sep 29, 2015
Messages
322
Office Version
  1. 365
Platform
  1. Windows
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 :giggle:
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Sort by date Sort by votes
@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.
 
Upvote 0
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
BC
2Void Loss - Working Days
3
4Enter year:2022/2023
5
6Enter TTD:20/07/2023
7
8Enter TSD:21/07/2023
9
10Working days: 
Calculator
Cell Formulas
RangeFormula
C10C10=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
CellAllowCriteria
C6Datebetween 01/01/1900 and 31/12/3000
C8Datebetween 01/01/1900 and 31/12/3000
C4List=UKBH!$B$2:$I$2


UKBH sheet (excuse my rambling formulas at the end!):
Void Loss Working Days Calculator.xlsx
BCDEFGHIJKLM
1UK Bank Holidays
22022/20232023/20242024/20252025/20262026/20272027/20282028/20292029/20302022/2023=IF(COUNTIF($B2:$I2,Calculator!$C$4,B2:I2,"")2022/2023
315/04/202207/04/202301/04/202418/04/202503/04/202629/04/202714/04/202802/04/202915/04/2022#N/A02/04/2029
418/04/202210/04/202306/05/202421/04/202506/04/202603/05/202717/04/202807/05/202918/04/202207/05/202907/05/2029
502/05/202201/05/202327/05/202405/05/202504/05/202631/05/202701/05/202828/05/202902/05/202228/05/202928/05/2029
602/06/202208/05/202326/08/202426/05/202525/05/202630/08/202729/05/202827/08/202902/06/202227/08/202927/08/2029
703/06/202229/08/202325/12/202425/08/202531/08/202627/12/202728/08/202825/12/202903/06/202225/12/202925/12/2029
829/08/202228/08/202326/12/202425/12/202525/12/202628/12/202725/12/202826/12/202929/08/202226/12/202926/12/2029
926/12/202225/12/202301/01/202526/12/202528/12/202603/01/202826/12/202801/01/193026/12/202201/01/193001/01/1930
1027/12/202226/12/202301/01/202601/01/202701/01/202927/12/2022
1102/01/202301/01/202426/03/202730/03/202902/01/2023
1229/03/2024 
UKBH
Cell Formulas
RangeFormula
M2:T2M2=IF(COUNTIF(B2:I2,Calculator!$C$4)>0,B2:I2)
L3L3=HLOOKUP(L2,B2:I12,10,0)
K3K3=IF(HLOOKUP($K$2,$B$2:$I$12,2,0)="","",HLOOKUP($K$2,$B$2:$I$12,2,0))
K4K4=IF(HLOOKUP($K$2,$B$2:$I$12,3,0)="","",HLOOKUP($K$2,$B$2:$I$12,3,0))
K5K5=IF(HLOOKUP($K$2,$B$2:$I$12,4,0)="","",HLOOKUP($K$2,$B$2:$I$12,4,0))
K6K6=IF(HLOOKUP($K$2,$B$2:$I$12,5,0)="","",HLOOKUP($K$2,$B$2:$I$12,5,0))
K7K7=IF(HLOOKUP($K$2,$B$2:$I$12,6,0)="","",HLOOKUP($K$2,$B$2:$I$12,6,0))
K8K8=IF(HLOOKUP($K$2,$B$2:$I$12,7,0)="","",HLOOKUP($K$2,$B$2:$I$12,7,0))
K9K9=IF(HLOOKUP($K$2,$B$2:$I$12,8,0)="","",HLOOKUP($K$2,$B$2:$I$12,8,0))
K10K10=IF(HLOOKUP($K$2,$B$2:$I$12,9,0)="","",HLOOKUP($K$2,$B$2:$I$12,9,0))
K11K11=IF(HLOOKUP($K$2,$B$2:$I$12,10,0)="","",HLOOKUP($K$2,$B$2:$I$12,10,0))
K12K12=IF(HLOOKUP($K$2,$B$2:$I$12,11,0)="","",HLOOKUP($K$2,$B$2:$I$12,11,0))
Dynamic array formulas.
 
Last edited:
Upvote 0
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! :LOL:

Void Loss Working Days Calculator.xlsx
BCDEFGHIJK
1UK Bank Holidays
22022/20232023/20242024/20252025/20262026/20272027/20282028/20292029/20302022/2023
315/04/202207/04/202301/04/202418/04/202503/04/202629/04/202714/04/202802/04/202915/04/2022
418/04/202210/04/202306/05/202421/04/202506/04/202603/05/202717/04/202807/05/202918/04/2022
502/05/202201/05/202327/05/202405/05/202504/05/202631/05/202701/05/202828/05/202902/05/2022
602/06/202208/05/202326/08/202426/05/202525/05/202630/08/202729/05/202827/08/202902/06/2022
703/06/202229/08/202325/12/202425/08/202531/08/202627/12/202728/08/202825/12/202903/06/2022
829/08/202228/08/202326/12/202425/12/202525/12/202628/12/202725/12/202826/12/202929/08/2022
926/12/202225/12/202301/01/202526/12/202528/12/202603/01/202826/12/202801/01/193026/12/2022
1027/12/202226/12/202301/01/202601/01/202701/01/202927/12/2022
1102/01/202301/01/202426/03/202730/03/202902/01/2023
1229/03/2024 
13
14910789797
15
UKBH
Cell Formulas
RangeFormula
K3K3=IF(HLOOKUP($K$2,$B$2:$I$12,2,0)="","",HLOOKUP($K$2,$B$2:$I$12,2,0))
K4K4=IF(HLOOKUP($K$2,$B$2:$I$12,3,0)="","",HLOOKUP($K$2,$B$2:$I$12,3,0))
K5K5=IF(HLOOKUP($K$2,$B$2:$I$12,4,0)="","",HLOOKUP($K$2,$B$2:$I$12,4,0))
K6K6=IF(HLOOKUP($K$2,$B$2:$I$12,5,0)="","",HLOOKUP($K$2,$B$2:$I$12,5,0))
K7K7=IF(HLOOKUP($K$2,$B$2:$I$12,6,0)="","",HLOOKUP($K$2,$B$2:$I$12,6,0))
K8K8=IF(HLOOKUP($K$2,$B$2:$I$12,7,0)="","",HLOOKUP($K$2,$B$2:$I$12,7,0))
K9K9=IF(HLOOKUP($K$2,$B$2:$I$12,8,0)="","",HLOOKUP($K$2,$B$2:$I$12,8,0))
K10K10=IF(HLOOKUP($K$2,$B$2:$I$12,9,0)="","",HLOOKUP($K$2,$B$2:$I$12,9,0))
K11K11=IF(HLOOKUP($K$2,$B$2:$I$12,10,0)="","",HLOOKUP($K$2,$B$2:$I$12,10,0))
K12K12=IF(HLOOKUP($K$2,$B$2:$I$12,11,0)="","",HLOOKUP($K$2,$B$2:$I$12,11,0))
B14:I14B14=COUNT(B3:B12)
 
Last edited:
Upvote 0
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?
 
Upvote 0
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
ABCDEF
1
2Void Loss - Working DaysHolidayList
32022-04-15
4Enter year:2022/20232022-04-18
52022-05-02
6Enter TTD:2022-07-202022-06-02
72022-06-03
8Enter TSD:2023-07-212022-08-29
92022-12-26
10Working days: 2022-12-27
112023-01-02
122023-04-07
13awoohaw's working days (Sat Sun Not worked)2552023-04-10
14Awoohaw's holidays between the dates2022-08-292023-05-01
152022-12-262023-05-08
162022-12-272023-08-29
172023-01-022023-08-28
182023-04-072023-12-25
192023-04-102023-12-26
202023-05-012024-01-01
212023-05-082024-03-29
222024-04-01
232024-05-06
242024-05-27
252024-08-26
262024-12-25
272024-12-26
282025-01-01
292025-04-18
302025-04-21
312025-05-05
322025-05-26
332025-08-25
342025-12-25
352025-12-26
362026-01-01
372026-04-03
382026-04-06
392026-05-04
402026-05-25
412026-08-31
422026-12-25
432026-12-28
442027-01-01
452027-03-26
462027-04-29
472027-05-03
482027-05-31
492027-08-30
502027-12-27
512027-12-28
522028-01-03
532028-04-14
542028-04-17
552028-05-01
562028-05-29
572028-08-28
582028-12-25
592028-12-26
602029-01-01
612029-03-30
622029-04-02
632029-05-07
642029-05-28
652029-08-27
662029-12-25
672029-12-26
681930-01-01
Calculator
Cell Formulas
RangeFormula
C10C10=IFERROR(IF($C6="","",IF($C8="",NETWORKDAYS($C6,TODAY()-1,UKBH!$K$3:$K$12),NETWORKDAYS($C6,$C8-1,UKBH!$K$3:$K$12))),"")
C13C13=NETWORKDAYS.INTL(C6,C8,1,HolidayList)
C14:C21C14=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
NameRefers ToCells
HolidayList=Calculator!$F$3:$F$68C13:C21
 
Upvote 0
Solution
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
BCDEFGHIJ
2Void Loss - Working DaysVoid Loss - Working DaysVoid Loss - Working Days
3
4Enter TTD:20/07/2022Enter TTD:07/05/2023Enter TTD:07/05/2023
5
6Enter TSD:21/07/2023Enter TSD:15/05/2023Enter TSD:15/05/2023
7
8Working days:255Working days:5Working days:4
9
10
1129/08/202215/04/202208/05/202307/04/2023
1229/08/202218/04/202208/05/202310/04/2023
1329/08/202202/05/202208/05/202301/05/2023
1429/08/202202/06/202208/05/202308/05/2023
1529/08/202203/06/202208/05/202329/08/2023
1629/08/202229/08/202208/05/202328/08/2023
1729/08/202226/12/202208/05/202325/12/2023
1829/08/202227/12/202208/05/202326/12/2023
1929/08/202202/01/202308/05/202301/01/2024
2029/08/202207/04/202308/05/202329/03/2024
2129/08/202210/04/202308/05/2023
2201/05/2023
2308/05/2023
2429/08/2023
2528/08/2023
2625/12/2023
2726/12/2023
2801/01/2024
2929/03/2024
3001/04/2024
3106/05/2024
3227/05/2024
3326/08/2024
3425/12/2024
3526/12/2024
3601/01/2025
3718/04/2025
3821/04/2025
3905/05/2025
4026/05/2025
4125/08/2025
4225/12/2025
4326/12/2025
4401/01/2026
4503/04/2026
4606/04/2026
4704/05/2026
4825/05/2026
4931/08/2026
5025/12/2026
5128/12/2026
5201/01/2027
5326/03/2027
5429/04/2027
5503/05/2027
5631/05/2027
5730/08/2027
5827/12/2027
5928/12/2027
6003/01/2028
6114/04/2028
6217/04/2028
6301/05/2028
6429/05/2028
6528/08/2028
6625/12/2028
6726/12/2028
6801/01/2029
6930/03/2029
7002/04/2029
7107/05/2029
7228/05/2029
7327/08/2029
7425/12/2029
7526/12/2029
7601/01/2030
Calculator
Cell Formulas
RangeFormula
C8,G8C8=NETWORKDAYS.INTL(C4,C6,1,HolidayList)
J8J8=IFERROR(IF($J4="","",IF($J6="",NETWORKDAYS($J4,TODAY()-1,$J11:$J20),NETWORKDAYS($J4,$J6-1,$J11:$J20))),"")
C11:C21C11=INDEX(HolidayList,MATCH($C$4,HolidayList,1)+1,1):INDEX(HolidayList,MATCH($C$6,HolidayList,1),1)
G11:G21G11=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
NameRefers ToCells
HolidayList=Calculator!$E$11:$E$76G8, C8, G11:G21, C11:C21
Cells with Data Validation
CellAllowCriteria
C4Datebetween 01/01/1900 and 31/12/3000
C6Datebetween 01/01/1900 and 31/12/3000
J4Datebetween 01/01/1900 and 31/12/3000
J6Datebetween 01/01/1900 and 31/12/3000
G4Datebetween 01/01/1900 and 31/12/3000
G6Datebetween 01/01/1900 and 31/12/3000
 
Upvote 0
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...:unsure:
 
Upvote 0
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:

Excel Formula: 
=IFERROR(IF($J4="","",IF($J6="",NETWORKDAYS($J4,TODAY()-1,    1   ,$J11:$J20),NETWORKDAYS($J4,$J6-1,   1    ,$J11:$J20))),"")
 
Upvote 0
awoohaw said:
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:

Excel Formula: 
=IFERROR(IF($J4="","",IF($J6="",NETWORKDAYS($J4,TODAY()-1,    1   ,$J11:$J20),NETWORKDAYS($J4,$J6-1,   1    ,$J11:$J20))),"")
Click to expand...
This is wrong, please ignore.
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Solved
Grabbing the latest data from a Column based on a cell
Replies
4
Views
262
PeteWright
PeteWright
S
  • Solved
Year from range of cells
Replies
9
Views
410
Fluff
Fluff
S
  • Solved
How to match and sum multiple cells
Replies
3
Views
141
Fluff
Fluff
S
  • Question
Formula for Equity Vesting
Replies
6
Views
343
Dave Patton
D
N
  • Question
2-step Dynamic Ranges and Charts
Replies
0
Views
208
Nick Loughton
N

Forum statistics

Threads
1,215,072
Messages
6,122,968
Members
449,095
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top