# Multi criteria lookup with the ''=lookup' formula

1. ## 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.  Register To Reply

2. ## 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  Register To Reply

3. ## 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.  Register To Reply

4. ## Re: Multi criteria lookup with the ''=lookup' formula Originally Posted by Ronnet2 =LOOKUP(2;1/(--(A:A>=D1)*--(B:B>=D2));A:A) Originally Posted by Ronnet2 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  Register To Reply

5. ## Re: Multi criteria lookup with the ''=lookup' formula Originally Posted by XOR LX 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  Register To Reply

6. ## Re: Multi criteria lookup with the ''=lookup' formula Originally Posted by Ronnet2 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  Register To Reply

7. ## Re: Multi criteria lookup with the ''=lookup' formula Originally Posted by XOR LX 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).  Register To Reply

8. ## 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  Register To Reply

9. ## 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)  Register To Reply

10. ## 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  Register To Reply

11. ## Re: Multi criteria lookup with the ''=lookup' formula Originally Posted by XOR LX 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?  Register To Reply

12. ## Re: Multi criteria lookup with the ''=lookup' formula Originally Posted by Ronnet2 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  Register To Reply

13. ## Re: Multi criteria lookup with the ''=lookup' formula Originally Posted by Ronnet2 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  Register To Reply

14. ## 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.  Register To Reply

15. ## 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.  Register To Reply

16. ## Re: Multi criteria lookup with the ''=lookup' formula

You're welcome!

Cheers  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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

#### 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