Results 1 to 12 of 12

Thread: "above lookup"

  1. #1
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    "above lookup"

    Hi,

    I'm trying to do an "above lookup" (same idea as a left lookup) based on how my data is organized. I've done left lookups before so I understand the use of MATCH and OFFSET for this.

    The problem is that I'm using a start/end date table to look up a date. Once I find the position within the table, I want to go to the row above the start-date row and get the corresponding "period number." The start/end dates have no pattern - eg, can't derive calendar quarters. In order to use MATCH with the -1 match_type, it seems that I have to have the table in descending order. But that is not consistent with how the dates need to be arranged.

    Basically my data looks like
    period------1-------2---------3---------4
    start----1/1/11---1/15/11--2/2/11---3/14/11
    end-----1/14/11--2/1/11---3/13/11--3/28/11

    So I want to be able to find which period a date like 1/13/11 belongs to.

    I know I can just do a test with IF on this but was hoping but something a little less ugly.

    TIA

    Fred

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Hi Fred - Could you setup a separate table for Dates and periods and use that for date and period lookups.

    See attached E2 and F2.
    Attached Files Attached Files

  3. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts

    HLookUp Version

    If you're willing to move your Period list from the top row to the bottom row you can use HLOOKUP.
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    What about just using MATCH:
    =MATCH(B6,$B$2:$F$2)

    If the periods are in the order of the dates. If not, you can use index and MATCH to lookup if you don't want to move the period row
    =INDEX($B$3:$F$3,MATCH(B6,$B$2:$F$2))

    Steve

  5. #5
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    tentative solution

    Hi Guys,

    I just had typed a rather long reply but found that when I clicked submit that I had been logged out! So I'm not about to retype it - maybe you're better for it.

    I'd rather not re-arrange the info since it logically should look like that. The period row represents part of a semester; the start and end dates show when the period started/ended. Period #s don't change; start/end dates do depending on the semester. These 3 rows are a header for info in rows below them.

    What I'm trying to do for a given HW or quiz given on a certain date is find in which period it falls.

    Also prefer not to use some hidden table.

    So thanks to RetiredGeek and tfspry.

    Steve's solution is actually what I tried first. But I was using the end dates (see below on this). Problem was that the period BEFORE the HW date is returned. This gets messy if the HW date is in the first period since #N/A is returned. I could trap that and add 1 for dates in the 2nd etc periods.

    I created a big ugly IF but didn't really like that. So I decided to use the start dates (if that's what Steve was suggesting). But the entry of the 1st start date is not reliable. The 2nd, 3rd, 4th start dates are just 1 day after the 1st, 2nd, 3rd end dates (so end_date+1). Entry of the end dates is not needed since they correspond to when the test is given and those must be entered elsewhere, so just using =test_date is ok.

    If anyone has any ideas on using the end dates, that would be appreciated. I tried a few things with MATCH (including some exotic array formulas), SMALL, MIN, and probably others.

    Thanks.

    Fred

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Yes I was using the startdate since when using lookups you need to use a starting number, not the ending.

    You can use the end but you will have to either move them or offset the value you get for the difference. You will also need a "end date" which occurs before the 1st period starts if there will be values in the 1st period [This can be the number zero, and anything before the first end date will be put in the first period]

    You can lookup on the date-1 in the match/lookup and it will hit the period before the enddate

    Steve

  7. #7
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Quote Originally Posted by sdckapr View Post
    Yes I was using the startdate since when using lookups you need to use a starting number, not the ending.
    That's what I figured.

    Quote Originally Posted by sdckapr View Post
    You can use the end but you will have to either move them or offset the value you get for the difference. You will also need a "end date" which occurs before the 1st period starts if there will be values in the 1st period [This can be the number zero, and anything before the first end date will be put in the first period]
    Not sure what you meant by "offset the value" or what "difference" you mean, but if I need an "artificial end date" then it's probably not worth it. There definitely will be values in the 1st period (HW gets assigned, quizzes get given). I know if I do a MATCH with a date in the first period, Excel will give me the #N/A error as I mentioned in my previous post. I assume your solution gets around that but right now I have a text label before the 1st end date. I was going to try "trapping" #N/A with something like an IF(ISERROR(...),1,period+1) but this is also something I wanted to avoid (it looks ugly and I'll never remember why I did that nor would I recall the meaning of any comment to myself).

    Quote Originally Posted by sdckapr View Post
    You can lookup on the date-1 in the match/lookup and it will hit the period before the enddate
    HUH?


    Thanks,

    Fred

  8. #8
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Fred - Can you attach an example of your data?

    thanks

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,829
    Thanks
    136
    Thanked 483 Times in 460 Posts
    Hi Fred

    re:"..In order to use MATCH with the -1 match_type.."

    You need to use the 1 match_type, NOT -1!

    Your period start dates are already in ascending order left-to-right.
    So use
    =MATCH(what, where, TRUE)

    "what" is the cell address containing the date you want the corresponding Period for;
    "where" is the horizontal cell range containing the start dates of each Period, (starting from Period 1).

    zeddy

  10. #10
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    sample

    Quote Originally Posted by tfspry View Post
    Fred - Can you attach an example of your data?

    thanks
    Attached is a sample. It's not the actual grade book but it has the relevant info. The first sheet has the period info, the "quiz" sheet has the dates of quizzes and a row into which I wanted to create a formula to retrieve the period number in which the quiz was given.

    As mentioned elsewhere, using start dates seems straightforward BUT I'd prefer to use END dates.

    Thanks.

    Fred
    Attached Files Attached Files

  11. #11
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Hi Zeddy,

    That works fine with the START dates with MATCH=1.

    See the attachment to my response to tfspry just posted a few minutes ago.

    I really prefer to use the END dates: "MATCH [match_type=-1] finds the smallest value that is greater than or equal to lookup_value" [from Excel Help]. This is exactly what I want.

    To use match_type=-1, the dates have to be in descending order (which would not be appropriate based on what I'm displaying; I know I could copy the dates to a "work area" and reverse the order).

    I tried something like
    {=match(quiz_date,x-b3:e3,-1)}.
    This is an array formula to force Excel to calculate a difference between the actual end dates and some "starting value" (called x here) to reverse the order of the "table" that Excel searches. If you use F9 on something like =match(quiz_date,b3:e3,1) while highlighting b3:e3, you'll see that Excel actually creates an array from those values. So I figured if I could do something like the array formula, I could get the order reversed. Problem is trying to figure what x is.

    Thanks.

    Fred

  12. #12
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts
    Folks,

    Thanks for all the help. I think I got it.

    I realized that what I was looking for was the end date which gave the smallest NEGATIVE difference when doing quiz_date - end_date.

    So my final formula is:
    {=MATCH(MIN(IF((I1-B4:E4)<=0,B4:E4,99999)),B4:E4,1)}
    where
    I1 is the cell with the quiz_date to be looked up
    B4:E4 is the array with period end dates where B4 has the date of the end of the 1st period, C4 has the end of the 2nd period, etc.

    So the IF statement returns an array of 4 values. If the quiz_date is beyond a period end date, 99999 is returned for that period (position in the array); if a period's end date is beyond the quiz_date, the period's end date is returned.

    Then by taking the min of the 4 values returned by the IF, I get the period whose end date is closest to, but AFTER, the quiz date (of course assuming we don't get up to real dates of 99999).

    Finally by doing a MATCH, I find where in the array of period end dates the MIN value occurs.

    I could then use the position to get the number of the period stored in the "period" row above the start/end dates using OFFSET but that isn't necessary here (only useful if something other than 1,2,3,4 stored there and I want whatever that might be).

    Comments? Improvements?

    Only downside is that the formula does not guard against dates outside the range. So a date before the 1st period still returns period 1 and a date beyond the 4th period returns period 4. While I could include an IF to check this, I figure if someone is entering a quiz_date before the semester begins or after it ends, then that's too bad!

    Fred
    Last edited by fburg; 2011-02-05 at 13:19. Reason: clarification

Posting Permissions

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