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.

 

[oldbrewer]

				01/01/2015	0
				02/01/2015	1
				03/01/2015	0
				04/01/2015	2
				05/01/2015	1
				06/01/2015	0
				07/01/2015	2	yes
				08/01/2015	1
				09/01/2015	0
				10/01/2015	2
				11/01/2015	1
				12/01/2015	0
		05/01/2015
	formula giving yes is
	=IF(AND(I11>=$G$18, J11=2,COUNTIF($K$1:K10,"yes")=0),"yes","")
	easy to make the 2 a cell ref so the formula will search for any combo
	once it has found the first one it will ignore all others

<colgroup><col span="2"><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[GregN]

I have a solution, but I don't know if it's particularly processor friendly... but it does work...
=MATCH(E2,INDIRECT("b"&MATCH(E1,A1:A12,1)&":b"&MATCH(MAX(A1:A12),A1:A12,0)),0)

This assumes that the list starts in A1, where A1 is the date and B1 is the number (0, 1 or 2) and that E1 is where the desired 'date' to lookup is and E2 is the desired 'number'.

This function uses the match and indirect functions, nested together... the function locates the first place where the required 'date' is. It then locates the end of the list assuming that the date column is sorted lowest to highest (thus the 'max' function). It then uses the match function to locate the where the 'number' is within that new range via the indirect function.

Greg

 

[Ronnet2]

Thanks for your response oldbrewer. Your formula requires an additional column to check. I should've included this, but that isn't possible in my case. I need to run similar logic tests hundreds of times. It wouldn't be possible to include additional columns for each. The sheet as described is sort of a reference sheet. On another sheet I have hundreds of combinations of dates + values. For each it needs to find the solution that meet the multi-criteria.

 

[AhoyNC]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Also, it's best not to refer to whole columns like A:A. If your data won't go beyond say row 100,000 then A1:A1000000 would probably speed things up.

=INDEX(A:A,MATCH(1,IF(A:A>=$D$1,IF(B:B>=$D$2,1)),0))

 

[Aladin Akyurek]

The following set up avoids whole column references AhoyNC mentions dynamically...

Row\Col	A​	B​	C​	D​
1​	1/1/2015​	0​	12​	5/1/2015​
2​	2/1/2015​	1​		2​
3​	3/1/2015​	0​
4​	4/1/2015​	2​		7/1/2015​
5​	5/1/2015​	1​
6​	6/1/2015​	0​
7​	7/1/2015​	2​
8​	8/1/2015​	1​
9​	9/1/2015​	0​
10​	10/1/2015​	2​
11​	11/1/2015​	1​
12​	12/1/2015​	0​

<tbody>
</tbody>

C1:

=MATCH(9.99999999999999E+307,A:A)

D4, control+shift+enter, not just enter:

=OFFSET($A$1,MATCH(1,IF($A$1:INDEX($A:$A,C1)>=D1,IF($B$1:INDEX($B:$B,C1)>=D2,1)),0)-1,0,1)

 

[Ronnet2]

Try:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Also, it's best not to refer to whole columns like A:A. If your data won't go beyond say row 100,000 then A1:A1000000 would probably speed things up.

=INDEX(A:A,MATCH(1,IF(A:A>=$D$1,IF(B:B>=$D$2,1)),0))

Thanks. Unfortunately it returns me the value of the date column header, no matter the chosen date or number.

I also prefer not to use arrays since I find they often stop working (but will use them if need be). FYI: all of this will be part of a simulation experiment which will also incorporate macros to save results/refresh/repeat.

 

[Ronnet2]

UPDATE: when I used a specified range (e.g. A2:A200000) Ahoy's formula did work. However, both ahoy and aladin's foruma do not provide the optimal answer if the optimal answer is the query date. For example, I search for 2-1-2014 and 4, and 2-1-2014 happens to be a 4, then it will skip this possible answer and look for the next possible answer. They are at least an improvement over my own attempts though

 

[Aladin Akyurek]

UPDATE: when I used a specified range (e.g. A2:A200000) Ahoy's formula did work. However, both ahoy and aladin's foruma do not provide the optimal answer if the optimal answer is the query date. For example, I search for 2-1-2014 and 4, and 2-1-2014 happens to be a 4, then it will skip this possible answer and look for the next possible answer. They are at least an improvement over my own attempts though

For the your exhibit which I reposted, I get #N/A for the look up values of 2-1-2014 and 4! If this is incorrect, what value do you expect to obtain for these look up values?

 

[Ronnet2]

For the your exhibit which I reposted, I get #N/A for the look up values of 2-1-2014 and 4! If this is incorrect, what value do you expect to obtain for these look up values?

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.