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.
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.
Last edited: