Multiple Criteria Lookup with Date Criteria

[keithmcvean]

I'm having a bit of an issue trying to figure this one out.

I have a tab with a similar structure to this:

Date Position Name
2015-01-11 Manager Sarah
2015-03-05 Worker Joe
2015-05-11 Manager Dave
2015-05-11 Worker Liz
2016-02-05 Worker Katie
2016-12-01 Manager Steve

The entries are in chronological order. What I'm trying to do is lookup a specific position where the date is less than or equal to today. So if I'm looking up a Manager it should come up Dave or a Worker Liz.

Any ideas? I know how to do multiple criteria lookups but not so much with this date criteria.

 

[Caribeiro77]

Would this work? if you put dates in chronological order i think it works, but if they are not in chronological order it may not work...

Book1
	A	B	C
1	Date	Position	Name
2	2015-01-11	Manager	Sarah
3	2015-03-05	Worker	Joe
4	2015-05-11	Manager	Dave
5	2015-05-11	Worker	Liz
6	2016-02-05	Worker	Katie
7	2016-12-01	Manager	Steve
8
9
10	Position	Name
11	Manager	Dave
Sheet1

Cell Formulas
Range	Formula
B11	{=INDEX(C2:C7,SMALL(IF(A2:A7<=TODAY(),IF(B2:B7=A11,ROW(A2:A7)-ROW(A2)+1)),ROW(A2:A7)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Are you interested only in the manager that is closest possible to today, not all managers prior to today? If so:

CONTROL+SHIF+enter, not just enter:

~

Row\Col	A​	B​	C​	D​	E​	F​
1​	Date	Position	Name		11/26/2015​	11/26/2015​
2​	1/11/2015​	Manager	Sarah		manager	worker
3​	3/5/2015​	Worker	Joe		Dave	Liz
4​	5/11/2015​	Manager	Dave
5​	5/11/2015​	Worker	Liz
6​	2/5/2016​	Worker	Katie
7​	12/1/2016​	Manager	Steve

<tbody>
</tbody>

In E2 control+shift+enter, not just enter, and copy across:

=IFERROR(INDEX($C$2:$C$7,1/(1/MAX(IF($A$2:$A$7 < E$1,IF($B$2:$B$7 = E$2,ROW($C$2:$C$7)-ROW($C$2)+1))))),"")<e$1,if($b$2:$b$7=e$2,row($c$2:$c$7)-row($c$2)+1))))),"")<strike></e$1,if($b$2:$b$7=e$2,row($c$2:$c$7)-row($c$2)+1))))),"")<strike>

 

[keithmcvean]

Thank you! This helps

 

[Aladin Akyurek]

Thank you! This helps

You are welcome.