+ Reply to Thread
Results 1 to 3 of 3

Check data in a table array that changes

  1. #1
    Registered User
    Join Date
    10-14-2011
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    9

    Check data in a table array that changes

    I have a column of dates that I want to check against a table of public holidays.

    Sheet 1:
    A B C
    Date
    Mon 23/04/2018 Formula
    Tue 24/04/2018 Formula
    Wed 25/04/2018 Formula
    Thu 26/04/2018 Formula
    Fri 27/04/2018 Formula
    Sat 28/04/2018 Formula
    Sun 29/04/2018 Formula

    Sheet2:
    Day 2017 2018 2019
    New Year's Day 01/01/2017 01/01/2018 01/01/2019
    New Year's Day 02/01/2017 01/01/2018 01/01/2019
    Australia Day 26/01/2017 26/01/2018 28/01/2019
    Labour Day 13/03/2017 12/03/2018 11/03/2019
    Good Friday* 14/04/2017 30/03/2018 19/04/2019
    Easter Sunday 16/04/2017 01/04/2018 21/04/2019
    Easter Monday 17/04/2017 02/04/2018 22/04/2019
    ANZAC Day* 25/04/2017 25/04/2018 25/04/2019
    Queens Bday 12/06/2018 11/06/2018 10/06/2019
    AFL Grand Final 29/09/2017 28/09/2018 27/09/2019
    Melbourne Cup 07/11/2017 06/11/2018 05/11/2019
    Christmas Day 25/12/2017 25/12/2018 25/12/2019
    Boxing Day 26/12/2017 26/12/2018 26/12/2019
    Boxing Day 26/12/2017 26/12/2018 26/12/2019

    I've tried a dozen formulas with no success.
    To get the column: MATCH(YEAR(B4),Sheet2!A:D,0)
    When i use vlookup it gives an error because the column changes based on the year of the data.
    I've tried index/match but I couldn't make that work. I can't figure out how to get a dynamic array to work.
    My goal: IF(B4) is found to be in Sheet2 then "PH" else "not PH"

    I think it should be a simple solution but none of the solutions I've researched can seem to solve this. I must be using the wrong keywords.

    I've searched for index/match but there was nothing and for dynamic array it is all about macros or too complex for my level of excel.
    Last edited by Baabaa; 10-18-2021 at 05:32 AM.

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,463

    Re: Check data in a table array that changes

    Use COUNTIF:

    =IF(COUNTIF(Sheet2!$A:$D,B4),"","not ")&"PH")
    Quang PT

  3. #3
    Registered User
    Join Date
    10-14-2011
    Location
    Pretoria, South Africa
    MS-Off Ver
    Excel 2010
    Posts
    9

    Re: Check data in a table array that changes

    Thank you for your simple solution. I don't know that function but it did exactly as required.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 12
    Last Post: 03-27-2019, 03:14 PM
  2. load data into array, check condition, and paste back into sheet
    By esbencito in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-18-2019, 06:46 AM
  3. Replies: 1
    Last Post: 12-02-2014, 05:46 AM
  4. Check of new data in a table
    By Subhradeep in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-23-2012, 07:37 AM
  5. macro to put zero's in table array - table array will vary in size each time
    By leanne2011 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-31-2012, 06:12 AM
  6. [SOLVED] How To: Check pivot table data source.
    By Kevin McCartney in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-03-2006, 05:30 AM
  7. [SOLVED] Pivot table how to check data source ?
    By Piotr in forum Excel General
    Replies: 1
    Last Post: 12-29-2005, 11:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1