+ Reply to Thread
Results 1 to 16 of 16

Multi criteria lookup with the ''=lookup' formula

  1. #1
    Registered User
    Join Date
    03-26-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    74

    Multi criteria lookup with the ''=lookup' formula

    Hi,

    I'm looking for a formula to provide me the answer to a multi-criteria lookup on a set range.

    The range looks like this:

    COLUMN A_____COLUMN B
    01-01-15______0
    02-01-15______1
    03-01-15______0
    04-01-15______2
    05-01-15______1
    06-01-15______0
    07-01-15______2
    08-01-15______1
    09-01-15______0
    10-01-15______2
    11-01-15______1
    12-01-15______0

    The first criteria is a date (e.g. 05-01-15), as see in column A, the second criteria is value (e.g. 2), as seen in column B. For example, I wish to find the first date that is >=05-01-15 and has a value >= 2. In the given range the answer be 07-01-15.

    I'm trying to make the following formula work for me:

    =LOOKUP(2;1/(--(A:A>=D1)*--(B:B>=D2));A:A)

    With D1: 05-01-15
    With D2: 2

    However, it is succesful in locating the last date that matches the multi criteria. In the example this is 10-01-15. So it does recognize a date that corresponds with a value of 2, but it starts the search 'bottom-up' instead of 'top-down'. Anyone know how to improve the formula (or have a better alternative)? I prefer a formula that isnt very resource demanding as the formula will be copy/pasted down a lot. Thank you.

  2. #2
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Multi criteria lookup with the ''=lookup' formula

    Hi.

    Please re-read the formula rules, particularly that related to "cross-posting".

    I'm sorry that you feel you have not yet received a satisfactory answer to your question on another forum, though I'm sure that you can understand the reasons for our policy regarding this matter.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    03-26-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    74

    Re: Multi criteria lookup with the ''=lookup' formula

    My apologies. Here is the link: http://www.mrexcel.com/forum/excel-q...ml#post4282124

    The solutions on the other forum have focused on alternative formula's, that so far haven't worked (yet). I'm looking for a solution with the ''=lookup'' because in my experience it isnt very demanding on resources. I suspect there has to be a way to easily tweak the formula I posted to make it start search topdown instead of bottom up.

  4. #4
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Multi criteria lookup with the ''=lookup' formula

    Quote Originally Posted by Ronnet2 View Post
    =LOOKUP(2;1/(--(A:A>=D1)*--(B:B>=D2));A:A)
    Quote Originally Posted by Ronnet2 View Post
    I'm looking for a solution with the ''=lookup'' because in my experience it isnt very demanding on resources.
    Actually, just as with other functions which operate over arrays, e.g. AGGREGATE, SUMPRODUCT, and any construction requiring CSE, the use of entire column references means that the formula you give will, on the contrary, be incredibly demanding on resource.

    On this forum you have the option of posting workbooks (the FAQ explains how). I suggest you do so and clearly outline your expected result(s).

    Regards

  5. #5
    Registered User
    Join Date
    03-26-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    74

    Re: Multi criteria lookup with the ''=lookup' formula

    Quote Originally Posted by XOR LX View Post
    Actually, just as with other functions which operate over arrays, e.g. AGGREGATE, SUMPRODUCT, and any construction requiring CSE, the use of entire column references means that the formula you give will, on the contrary, be incredibly demanding on resource.

    On this forum you have the option of posting workbooks (the FAQ explains how). I suggest you do so and clearly outline your expected result(s).

    Regards
    They're very demanding, true enough but light compared to combinations of 'MATCH&IF' formula's.

    Here is an example file. The actual list is much longer but I shortened it to stay under the MB limit: lookup problem.xlsx

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Multi criteria lookup with the ''=lookup' formula

    Quote Originally Posted by Ronnet2 View Post
    They're very demanding, true enough but light compared to combinations of 'MATCH&IF' formula's.
    No. The issue is not so much the combination of functions. The use of entire column references within any array-processing function is a crime in Excel terms. Why force Excel to calculate millions of cells more than are actually necessary?

    What is your expected result for the attached, please?

    Regards

  7. #7
    Registered User
    Join Date
    03-26-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    74

    Re: Multi criteria lookup with the ''=lookup' formula

    Quote Originally Posted by XOR LX View Post
    No. The issue is not so much the combination of functions. The use of entire column references within any array-processing function is a crime in Excel terms. Why force Excel to calculate millions of cells more than are actually necessary?

    What is your expected result for the attached, please?

    Regards
    I understand what you mean. Once I've determined the theoretical maximum range size, then I'll adapt the array to this size. But whether the range is A:A or A1:A100000, I feel that lookup works more efficient than a combination of match & if on the same range.

    The expected result is a formula that returns the date that meets both the date and value criteria. The criteria and formula expected results are described within the file. As well as an example of the formula I described 'in action' (i.e. finding a date that matches both criteria but doing it bottomup).
    Last edited by Ronnet2; 09-17-2015 at 05:46 AM. Reason: grammar

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Multi criteria lookup with the ''=lookup' formula

    Sorry - I meant: for that data, what is your manually-calculated expected result, just so that I can verify that I arrive at the same answer with any solution.

    Regards

  9. #9
    Registered User
    Join Date
    03-26-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    74

    Re: Multi criteria lookup with the ''=lookup' formula

    I see. Well, in the file the criteria are set on >=1-1-15 at 5:00, and >=7. Given the sample data, the solution should be 1-1-15 at 14:00, this is the first date that meets both criteria.

    Of course there are no values of 7 before this particular date anyways, so maybe its better to change the criteria to D1: 02-01-15 05:00:00 and D2: 7. Now the value of 7 at 1-1-15 14:00:00 is outside the date criteria. The expected result becomes 03-01-15 03:00:00 (i.e. the first date with value of 7 in column B with a date in column A that is >=02-01-15 05:00:00)

  10. #10
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Multi criteria lookup with the ''=lookup' formula

    I'm afraid you can't avoid some form of array processing formula, but rest assured that this will not be much less efficient than a set-up involving LOOKUP (which in any case cannot be employed here):

    =INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$128)/((A$2:A$128>=D2)*(B$2:B$128>=D3)),1))

    And don't change the references in the above to entire columns!

    Regards

  11. #11
    Registered User
    Join Date
    03-26-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    74

    Re: Multi criteria lookup with the ''=lookup' formula

    Quote Originally Posted by XOR LX View Post
    I'm afraid you can't avoid some form of array processing formula, but rest assured that this will not be much less efficient than a set-up involving LOOKUP (which in any case cannot be employed here):

    =INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$128)/((A$2:A$128>=D2)*(B$2:B$128>=D3)),1))

    And don't change the references in the above to entire columns!

    Regards
    It works! Well done!! Don't worry, I'll use a maximum array from now on

    EDIT: Actually, there is an entire column array in your formula too And when I change it to a specific array, the formula always provides an answer one lower then the correct answer. So if 14:00 is the answer, then the formula provides 15:00 as the answer. It does provide 14:00 if I set it back to entire column array.

    I'm curious though, why won't lookup work? Does lookup always work bottomup?

    Because I understand what the formula does. It goes through the entire list for values that match both criteria and if it does then it assigns 'true' to them. Then it divided 1/true = 1, and if it isnt true then its 1/error = error. There is nothing in the formula that dictates that it should start bottomup. So is it native to the lookup formula?
    Last edited by Ronnet2; 09-17-2015 at 06:29 AM. Reason: Issue with formula

  12. #12
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Multi criteria lookup with the ''=lookup' formula

    Quote Originally Posted by Ronnet2 View Post
    So is it native to the lookup formula?
    Correct. If the lookup_value is not found within the lookup_vector then LOOKUP returns the last value within the lookup_vector which is less than the lookup_value.

    Since, after reciprocation with unity, an array of Boolean TRUE/FALSE values will become an array in which the only entries are either 1 or #DIV/0!, any value greater than 1 will thus suffice as the choice for the lookup_value. Which is why you see 2 in this construction, though of course any other value >1 would be equally good.

    Regards

  13. #13
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Multi criteria lookup with the ''=lookup' formula

    Quote Originally Posted by Ronnet2 View Post
    EDIT: Actually, there is an entire column array in your formula too And when I change it to a specific array, the formula always provides an answer one lower then the correct answer. So if 14:00 is the answer, then the formula provides 15:00 as the answer. It does provide 14:00 if I set it back to entire column array.
    Yes, but the formula is not processing all cells within that array, simply INDEXing it, which is a crucial difference.

    When INDEXing column A - which by default begins in row 1, passing A$2:A$128 to ROW in our construction will be sufficient. If, however, you wish to use the same range everywhere in your formula (and I wouldn't blame you: a perfectly normal requirement, particularly if, for example, your range is stored as a Named Range), then we need to be a bit more considerate, viz:

    =INDEX(A$2:A$128,AGGREGATE(15,6,(ROW(A$2:A$128)-MIN(ROW(A$2:A$128))+1)/((A$2:A$128>=D2)*(B$2:B$128>=D3)),1))

    I generally prefer the first option if, for example, I am not using a Named Range, since clearly there is less calculation involved.

    Of course, if you do not make these amendments, and try to use simply:

    =INDEX(A$2:A$128,AGGREGATE(15,6,ROW(A$2:A$128)/((A$2:A$128>=D2)*(B$2:B$128>=D3)),1))

    you will get incorrect results, since you are now INDEXing beginning at row 2, not row 1 (as is the case when A:A is used as the array being passed to INDEX).

    If you've never seen this construction before, it's a very useful one, the point being that, in general, no matter where in the worksheet Range is, the set-up:

    ROW(Range)-MIN(ROW(Range))+1

    will always produce an array of integers from 1 up to the number of rows within Range, which is necessary when passing to INDEX (since that function returns a value with respect to relative, not absolute row positions).

    Regards

  14. #14
    Registered User
    Join Date
    03-26-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    74

    Re: Multi criteria lookup with the ''=lookup' formula

    Thank you for your explaination. I'm considered the Excel expert in some circles, but clearly I'm nothing close to it in other circles

    I will stick to your original formula since it meets my purpose just fine. I will certainly be saving this page in my favorites.

  15. #15
    Registered User
    Join Date
    03-26-2015
    Location
    Netherlands
    MS-Off Ver
    2013
    Posts
    74

    Re: Multi criteria lookup with the ''=lookup' formula

    Thank you for your explaination. I'm considered the Excel expert in some circles, but clearly I'm nothing close to it in other circles

    I will stick to your original formula since it meets my purpose just fine. I will certainly be saving this page in my favorites.

  16. #16
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,508

    Re: Multi criteria lookup with the ''=lookup' formula

    You're welcome!

    Cheers

+ 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. Need help with creating a multi-criteria lookup formula
    By mikem24 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-23-2015, 10:52 PM
  2. Multi-Criteria Column Lookup
    By nielsen555 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-23-2015, 12:11 PM
  3. Help : Multi criteria with MATCH or LOOKUP formula
    By clonedarkman in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-20-2014, 10:47 AM
  4. Multi Criteria Lookup
    By jsabo in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-27-2013, 02:06 AM
  5. Complex multi criteria lookup and count formula???
    By JapanDave in forum Excel General
    Replies: 9
    Last Post: 05-25-2009, 04:36 AM
  6. Multi criteria lookup
    By spot1960 in forum Excel General
    Replies: 2
    Last Post: 04-08-2008, 10:33 AM
  7. Multi Criteria lookup
    By epotter in forum Excel General
    Replies: 3
    Last Post: 07-05-2006, 07:35 PM

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