+ Reply to Thread
Results 1 to 13 of 13

Find first table entry meeting several criteria

  1. #1
    Registered User
    Join Date
    06-27-2011
    Location
    Australia
    MS-Off Ver
    Excel 2011
    Posts
    5

    Find first table entry meeting several criteria

    I have what I think is a simple query but I cannot find anyone else asking the question so here goes.

    I have a table showing the price of an item each day over a period of time. The table has a Date column (sorted ascending) and a Price column. The price goes up and down from day to day.
    Given a specific date, I need to find the next date when the price dropped below that price.
    So I need to find a row in the table and return the date column based on 2 criteria being Date > given date and Price < given price.
    From what I can find VLookup will only find a table entry if it is a specific value.
    I have also looked in Index Match but similarly I am not trying to Match a value but rather a condition.
    Any assistance on what formula I could use would be greatly appreciated.

    As an example if I was given 07Jan13 Price = 21, I need the formula to return 15Jan13 as this was the next date the Price was less than 21.
    Date Price
    31-Dec-12 20
    02-Jan-13 24
    03-Jan-13 19
    04-Jan-13 22
    07-Jan-13 21
    08-Jan-13 22
    11-Jan-13 23
    12-Jan-13 21
    15-Jan-13 20
    16-Jan-13 18
    18-Jan-13 17

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Find first table entry meeting several criteria

    Suppose you put your starting date in E1, with labels like "start date" in D1 and "next date price lower" in D2, then you could have this array* formula in E2:

    =IF(E1="","",INDEX(A:A,MIN(IF(INDIRECT("B"&MATCH(E1,A:A,0)&":B12")<INDEX(B:B,MATCH(E1,A:A,0)),ROW(INDIRECT("B"&MATCH(E1,A:A,0)&":B12"))))))

    to give you that date.

    *An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual <Enter>

    Hope this helps.

    Pete

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Find first table entry meeting several criteria

    Hi glenn,

    If Pete's formula is too long for you then try this one that also works:

    =INDEX(A2:A12,MIN(IF(((A2:A12>E1)*(B2:B12<INDEX($B$2:$B$12,MATCH(E$1,$A$2:$A$12,0)))*(ROW(A2:A12)))=0,"",((A2:A12>E1)*(B2:B12<E2)*(ROW(A2:A12)-1)))))

    Mine is also a CSE formula where you need to enter Control+Shift+Enter to make it work.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    06-27-2011
    Location
    Australia
    MS-Off Ver
    Excel 2011
    Posts
    5

    Re: Find first table entry meeting several criteria

    Pete_UK
    Thanks for your reply. I have tried it and it works so thanks. Now the question is how? I can understand the IF, INDEX and MIN formulas and the logic of selecting the MIN row where the Price is smaller than the Price on the Date. The internal IF formula is doing something that I was not expecting and that is working on a range. My simplified version is IF(value of Bn:B12<Bn, return the Row number). Can you explain what this is doing as I cannot get my head around it.
    Thanks
    Glenn

  5. #5
    Registered User
    Join Date
    06-27-2011
    Location
    Australia
    MS-Off Ver
    Excel 2011
    Posts
    5

    Re: Find first table entry meeting several criteria

    Marvin,
    Thanks for your solution. I tried it and it works but also I do not understand why. You have an IF statement in the middle of the formula which essentially is multiplying dates and prices together.
    (A2:A12>E1)*(B2:B12<E2n)*(ROW(A2:A12)-1) What is that all about?
    Thanks Glenn

  6. #6
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Find first table entry meeting several criteria

    The "*" sign means AND in this case, because we are doing the CSE formula.

    The first part of the formula is to find the Value associated with the date: That is:
    MATCH(E$1,$A$2:$A$12,0) - which gives the row that the date you put in E1 is in.
    Then the Index of the above gives the number to the right of that date.

    So this formula in English starts out like:
    Find the Row that has a Date greater than the one you put in E1 AND has a Value less than the one found in that Index/Match stuff above.

    The above gives stuff like zero for false and 1 for true. We have two sets of 0,0,0,1,1,.. and 0,1,0,1,... from the (A2:A12>E1) AND B2:B12<E2
    Then by tossing in the Row(A2:A12)-1 , we get 1,2,3,4,5,6,7,8,...

    I convert all the zero where these three arrays are multiplied together to blank so I end up with the MINIMUM of the number of the Row where both the < and > were true. I use this row in the Index function to return the desired date.

    This kind of stuff is why we get the guru hat to wear and sit in the corner....

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Find first table entry meeting several criteria

    Quote Originally Posted by MarvinP View Post
    ... If Pete's formula is too long for you then try this one ...
    Marvin,

    your formula is actually longer than mine, and you are not checking that E1 is empty <bg>

    Thanks for following up while I was asleep.

    Pete

  8. #8
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Find first table entry meeting several criteria

    Hey Pete, I was being a little sarcastic by the length of our answers. It took me a very long time to "trial and error" my formula and then saw you had a shorter one. You even checked for blanks and my formula didn't. I'm not sure the OP will ever really use our work but that is what they asked for. Nice formula!! BTW - I just got up!!

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,603

    Re: Find first table entry meeting several criteria

    Hi Marvin,

    I felt it needed an IFERROR around it, in case you chose the last date in the list, or if the price did not drop after the chosen date - let's wait for the OP to get back.

    Pete

  10. #10
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Find first table entry meeting several criteria

    Hey Pete,

    We both spent some time with this problem and it is one of the harder formulas. I think if the OP sorted by dates and then by Values (large to small) it would be a snap to do. Because they sorted by date only it made it a lot harder. I don't think the OP will come back to this. Even if they do, explaining the answer is over most of the good user's heads.

  11. #11
    Registered User
    Join Date
    06-27-2011
    Location
    Australia
    MS-Off Ver
    Excel 2011
    Posts
    5

    Re: Find first table entry meeting several criteria

    Marvin, Pete,
    I am assuming I am an OP, but I don't know what it stands for.....operator?
    Marvin, you indicated that it would be simpler if it was sorted by dates then values. It is in a way, as there is only one price/value per date. Perhaps I could have made this clearer. So how could the query be simpler?
    Thank you both for your assistance in the above.
    For what is essentially a simple query, it is a pity that Excel does not provide a simple function.
    Regards
    Glenn

  12. #12
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: Find first table entry meeting several criteria

    Hi glenn,

    See the attached where sorting the data first by date and then by amount puts the date you want directly below the input date.
    Attached Files Attached Files

  13. #13
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365 v 2403
    Posts
    13,396

    Re: Find first table entry meeting several criteria

    FWIW here's another approach (J1 is a reference cell where you can "store" 1/7/2015 or whatever):

    =INDEX($F$1:$F$11,MATCH(TRUE,IF($F$1:$F$11>=$J$1,$G$1:$G$11)<MAX(IF($J$1=$F$1:$F$11,$G$1:$G$11)),0))

    It took me forever to dumb this one out. MAX turned out to be the hidden key for me.

    Edit: I decided to let errors ride. NA is more alarming than "". LOL
    Last edited by FlameRetired; 03-06-2015 at 04:25 AM.

+ 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. [SOLVED] find value meeting certain criteria in a range and return that value
    By bardobhb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-21-2014, 02:03 PM
  2. Find a column reference for a cell meeting 2-dimensional criteria
    By crazytalker90 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 09-06-2013, 10:28 AM
  3. Create a list from a table meeting certain criteria
    By gajaburu in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-07-2013, 08:47 AM
  4. [SOLVED] How can a find the row number of a row meeting specified criteria?
    By satania in forum Excel General
    Replies: 2
    Last Post: 05-29-2012, 10:12 AM
  5. Replies: 6
    Last Post: 09-30-2009, 12:05 PM

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