Multi criteria lookup

[Ronnet2]

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.

 

[Aladin Akyurek]

I'm not sure if I understand the question, but both criteria are '>='. So if I'm looking for the first date that matches ''>=2-1-2015' and '4' and by coincidence '2-1-15' happens to have a '4' then the answer should be '2-1-2015'. Only if 2-1-2015 has a 3 or lower should the formula start looking for the first date that has '>=4' and return this date as the answer.

Post #6, which is a re-post of your sample in post #1, contains no 4 in the B-range...

 

[Ronnet2]

Post #6, which is a re-post of your sample in post #1, contains no 4 in the B-range...

Sorry that it wasnt clear, columm B can go up quite high. The set is just an example. Here is another example of what could be the content of column B:

0
0
1
0
2
1
0
10
9
8
7
6
5
4
3
2
1
0
5
4
3
2
1
0
2
1
0
2
1
0

So if Im looking for >=10 then there is only one 'date' that matches this, which belongs to the 8th value in column B. If I'm looking for >=5 then there are two options. Andd for >=2 then there are many more options.

 

[Ronnet2]

Solved in this thread: Multi criteria lookup with the ''=lookup' formula

Thanks everyone for your thoughts and time