lookup value based on two criteria

[jgregory317]

Hey folks. I want to lookup a date in a table based on two criteria: name and date. There are lots of non-unique (repeating) values in the name column. I want to lookup the date based on it being less than a given date. For example: In the data below I want the formula to return the most recent date less than or equal to 15-Feb for john. I want the formula to return 14-Feb. My actual data is sorted by date. It's a record of dates when a client was visited. Thanks in advance.

john 13-Feb
john 14-Feb
beth 14-Feb
peter 14-Feb
leslie 16-Feb
john 17-Feb

[AliGW]

Welcome to the forum!

Try this:

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	john	13-Feb		15-Feb	John
2	john	14-Feb
3	beth	14-Feb			14/02/2018
4	peter	14-Feb
5	leslie	16-Feb
6	john	17-Feb

Sheet: Sheet2

Excel 2016 (Windows) 32 bit

	E
3	=LOOKUP(2,1/(($A$1:$A$6=E1)*($B$1:$B$6<=D1)),$B$1:$B$6)

Sheet: Sheet2

[jgregory317]

You are my hero! Thank you! What does the 2 (the first argument) in the lookup formula do?

[jgregory317]

Nevermind... found the answer... "If you use 2 as the lookup value, then the formula will match it with the last numeric value in the range, that is, the last row where both conditions are True."

[AliGW]

Correct! And sorry I didn't see your question until just now. Glad to have helped!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.