+ Reply to Thread
Results 1 to 7 of 7

LOOKUP Having one Exact matching criteria and one between 2 date ranges.

  1. #1
    Registered User
    Join Date
    10-23-2012
    Location
    Colchester
    MS-Off Ver
    Excel 2007
    Posts
    3

    LOOKUP Having one Exact matching criteria and one between 2 date ranges.

    Any help would be gratefully appreciated.

    I have a set of data which 3 Columns: Client ID, Visit Date, and Status see below

    A B C D E F G
    1 Client ID Visit Date Status Client ID Visit Date From Visit Date To Status
    2 100 01/08/2012 100 01/08/2012 04/08/2012 Holiday
    3 100 02/08/2012 100 05/08/2012 07/08/2012 Respite
    4 100 03/08/2012 200 04/08/2012 06/08/2012 Holiday
    5 100 04/08/2012
    6 100 05/08/2012
    7 100 06/08/2012
    8 100 07/08/2012
    9 200 01/08/2012
    10 200 02/08/2012
    11 200 03/08/2012
    12 200 04/08/2012
    13 200 05/08/2012
    14 200 06/08/2012
    15 200 07/08/2012

    Is there a way to match exactly on columns A and E and also match between dates where column B is between Columns E and F and return the value from column G and place it in Column C where matched.

    Thanks and regards Grahame Hamilton

  2. #2
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,679

    Re: LOOKUP Having one Exact matching criteria and one between 2 date ranges.

    Please attach a sample workbook with expected output for getting quick solution.


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  3. #3
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: LOOKUP Having one Exact matching criteria and one between 2 date ranges.

    =lookup(2,1/((D:D=A2)*(E:E<=B2)*(F:F>=B2)),G:G)
    adjust ranges to match your data rows
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  4. #4
    Registered User
    Join Date
    10-23-2012
    Location
    Colchester
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: LOOKUP Having one Exact matching criteria and one between 2 date ranges.

    Thanks Joseph, Works a treat with a small number of rows, but struggles with alot.
    I have maybe around 15,000 to 20,000 rows to calculate and it seems to sit for ages trying to calculate.
    Is there another method or formula I could try?

  5. #5
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: LOOKUP Having one Exact matching criteria and one between 2 date ranges.

    if your lookup table is-or can be-sorted by clientid we could use a longer formula but restrict the lookup to smaller ranges. a sample workbook would make it much easier to demonstrate but simply put you would use a match formula to find the first row number for the relevant id, then countif to find how many rows to use and then adjust the lookup to only search those rows (using index functions)

  6. #6
    Registered User
    Join Date
    10-23-2012
    Location
    Colchester
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: LOOKUP Having one Exact matching criteria and one between 2 date ranges.

    Hello Joseph,

    Followed your advise in adjusting the data rows. so formula now reads.

    =IFERROR(LOOKUP(2,1/((Diary!$D$8:$D$250=F9)*(Diary!$G$8:$G$250<=L9)*(Diary!$H$8:$H$250>=L9)),Diary!$I$8:$I$250),"")

    added a little iferror tidyup! and because in reality the diary data will only hold no more than 250 rows and they start at row 8 - it now does not need to look to the bottom of the sheet!. Works a treat.

    Thanks Very much Joseph. btw what does the 2,1 do?

    Regards Grahame Hamilton

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: LOOKUP Having one Exact matching criteria and one between 2 date ranges.

    the middle part of the function
    ((D:D=A2)*(E:E<=B2)*(F:F>=B2))
    returns an array of 1 and 0 values (each section returns arrays of TRUE/FALSE and when multiplied they are treated as 1/0 respectively)

    dividing 1 by this array returns an array of values that are either 1 or DIV/0 errors (LOOKUP ignores error values)

    looking up 2 in this array matches the last instance of 1 (there should presumably only be one 1 value at most for a given set of criteria) and returns the corresponding value from column g

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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