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

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

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

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).

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

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

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?

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

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

You're welcome!

Cheers