lookup a value in date range

[jbrosch]

Trying to look up EmplId and Pay End Date in range below and return appropriate Job Title.

[TABLE="width: 347"]
<tbody>[TR]
[TD]Job Title
[/TD]
[TD]Emplid
[/TD]
[TD]Pay End Date
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]101
[/TD]
[TD]1/3/2009
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]101
[/TD]
[TD]6/6/2009
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]101
[/TD]
[TD]12/5/2009
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]101
[/TD]
[TD]6/18/2011
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]101
[/TD]
[TD]8/25/2012
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]467
[/TD]
[TD]12/29/2012
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]467
[/TD]
[TD]1/12/2013
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]467
[/TD]
[TD]4/6/2013
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]118
[/TD]
[TD]12/17/2011
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]118
[/TD]
[TD]12/31/2011
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]118
[/TD]
[TD]1/14/2012
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]118
[/TD]
[TD]2/23/2013
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]758
[/TD]
[TD]3/12/2011
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]758
[/TD]
[TD]1/14/2012
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]758
[/TD]
[TD]12/1/2012
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]758
[/TD]
[TD]11/16/2013
[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 617"]
<tbody>[TR]
[TD]Empl_Id
[/TD]
[TD]Job_Title
[/TD]
[TD]Begin_Date
[/TD]
[TD]End_Date
[/TD]
[/TR]
[TR]
[TD]101
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2009
[/TD]
[TD]12/31/2009
[/TD]
[/TR]
[TR]
[TD]101
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2010
[/TD]
[TD]12/31/2010
[/TD]
[/TR]
[TR]
[TD]101
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2011
[/TD]
[TD]12/31/2011
[/TD]
[/TR]
[TR]
[TD]101
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2012
[/TD]
[TD]12/31/2012
[/TD]
[/TR]
[TR]
[TD]101
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2013
[/TD]
[TD]12/31/2013
[/TD]
[/TR]
[TR]
[TD]118
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2010
[/TD]
[TD]12/31/2010
[/TD]
[/TR]
[TR]
[TD]118
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2011
[/TD]
[TD]12/31/2011
[/TD]
[/TR]
[TR]
[TD]118
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2012
[/TD]
[TD]4/30/2012
[/TD]
[/TR]
[TR]
[TD]118
[/TD]
[TD]AREA MANAGER
[/TD]
[TD]5/1/2012
[/TD]
[TD]12/31/2012
[/TD]
[/TR]
[TR]
[TD]118
[/TD]
[TD]AREA MANAGER
[/TD]
[TD]1/1/2013
[/TD]
[TD]3/31/2013
[/TD]
[/TR]
[TR]
[TD]467
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2011
[/TD]
[TD]12/31/2011
[/TD]
[/TR]
[TR]
[TD]467
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2012
[/TD]
[TD]12/31/2012
[/TD]
[/TR]
[TR]
[TD]467
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2013
[/TD]
[TD]3/31/2013
[/TD]
[/TR]
[TR]
[TD]467
[/TD]
[TD]CONVICT
[/TD]
[TD]4/1/2013
[/TD]
[TD]12/31/2013
[/TD]
[/TR]
[TR]
[TD]758
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2010
[/TD]
[TD]12/31/2010
[/TD]
[/TR]
[TR]
[TD]758
[/TD]
[TD]BRANCH SALES MANAGER
[/TD]
[TD]1/1/2011
[/TD]
[TD]12/31/2011
[/TD]
[/TR]
[TR]
[TD]758
[/TD]
[TD]BROADCASTER
[/TD]
[TD]1/1/2012
[/TD]
[TD]2/29/2012
[/TD]
[/TR]
[TR]
[TD]758
[/TD]
[TD]STATION OWNER
[/TD]
[TD]3/1/2012
[/TD]
[TD]12/31/2012
[/TD]
[/TR]
[TR]
[TD]758
[/TD]
[TD]STATION OWNER
[/TD]
[TD]1/1/2013
[/TD]
[TD]12/31/2013
[/TD]
[/TR]
</tbody>[/TABLE]

Any help is appreciated.

 

[Weazel]

maybe something like.....

Excel 2012

	A	B	C	D	E	F	G	H
BRANCH SALES MANAGER	BRANCH SALES MANAGER
BRANCH SALES MANAGER	BRANCH SALES MANAGER
BRANCH SALES MANAGER	BRANCH SALES MANAGER
BRANCH SALES MANAGER	test1
test1	BRANCH SALES MANAGER
test 3	BRANCH SALES MANAGER
BRANCH SALES MANAGER	BRANCH SALES MANAGER
CONVICT	BRANCH SALES MANAGER
BRANCH SALES MANAGER	AREA MANAGER
BRANCH SALES MANAGER	test2
BRANCH SALES MANAGER	BRANCH SALES MANAGER
test2	test 3
BRANCH SALES MANAGER	BRANCH SALES MANAGER
BROADCASTER	CONVICT
STATION OWNER	BRANCH SALES MANAGER
STATION OWNER	BRANCH SALES MANAGER
BROADCASTER
STATION OWNER
STATION OWNER

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00"]Job Title[/TD]
[TD="bgcolor: #FFFF00"]Emplid[/TD]
[TD="bgcolor: #FFFF00"]Pay End Date[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #FFFF00"]Empl_Id[/TD]
[TD="bgcolor: #FFFF00"]Job_Title[/TD]
[TD="bgcolor: #FFFF00"]Begin_Date[/TD]
[TD="bgcolor: #FFFF00"]End_Date[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"]1/3/2009[/TD]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]1/1/2009[/TD]
[TD="align: right"]12/31/2009[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"]6/6/2009[/TD]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]1/1/2010[/TD]
[TD="align: right"]12/31/2010[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"]12/5/2009[/TD]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]1/1/2011[/TD]
[TD="align: right"]12/31/2011[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"]6/18/2011[/TD]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]101[/TD]
[TD="align: right"]8/25/2012[/TD]
[TD="align: right"][/TD]
[TD="align: right"]101[/TD]

[TD="align: right"]1/1/2013[/TD]
[TD="align: right"]12/31/2013[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]467[/TD]
[TD="align: right"]12/29/2012[/TD]
[TD="align: right"][/TD]
[TD="align: right"]118[/TD]

[TD="align: right"]1/1/2010[/TD]
[TD="align: right"]12/31/2010[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]467[/TD]
[TD="align: right"]1/12/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]118[/TD]

[TD="align: right"]1/1/2011[/TD]
[TD="align: right"]12/31/2011[/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]467[/TD]
[TD="align: right"]4/6/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]118[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]4/30/2012[/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]118[/TD]
[TD="align: right"]12/17/2011[/TD]
[TD="align: right"][/TD]
[TD="align: right"]118[/TD]

[TD="align: right"]5/1/2012[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]118[/TD]
[TD="align: right"]12/31/2011[/TD]
[TD="align: right"][/TD]
[TD="align: right"]118[/TD]

[TD="align: right"]1/1/2013[/TD]
[TD="align: right"]3/31/2013[/TD]

[TD="align: center"]12[/TD]

[TD="align: right"]118[/TD]
[TD="align: right"]1/14/2012[/TD]
[TD="align: right"][/TD]
[TD="align: right"]467[/TD]

[TD="align: right"]1/1/2011[/TD]
[TD="align: right"]12/31/2011[/TD]

[TD="align: center"]13[/TD]

[TD="align: right"]118[/TD]
[TD="align: right"]2/23/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]467[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: center"]14[/TD]

[TD="align: right"]758[/TD]
[TD="align: right"]3/12/2011[/TD]
[TD="align: right"][/TD]
[TD="align: right"]467[/TD]

[TD="align: right"]1/1/2013[/TD]
[TD="align: right"]3/31/2013[/TD]

[TD="align: center"]15[/TD]

[TD="align: right"]758[/TD]
[TD="align: right"]1/14/2012[/TD]
[TD="align: right"][/TD]
[TD="align: right"]467[/TD]

[TD="align: right"]4/1/2013[/TD]
[TD="align: right"]12/31/2013[/TD]

[TD="align: center"]16[/TD]

[TD="align: right"]758[/TD]
[TD="align: right"]12/1/2012[/TD]
[TD="align: right"][/TD]
[TD="align: right"]758[/TD]

[TD="align: right"]1/1/2010[/TD]
[TD="align: right"]12/31/2010[/TD]

[TD="align: center"]17[/TD]

[TD="align: right"]758[/TD]
[TD="align: right"]11/16/2013[/TD]
[TD="align: right"][/TD]
[TD="align: right"]758[/TD]

[TD="align: right"]1/1/2011[/TD]
[TD="align: right"]12/31/2011[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]758[/TD]

[TD="align: right"]1/1/2012[/TD]
[TD="align: right"]2/29/2012[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]758[/TD]

[TD="align: right"]3/1/2012[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]758[/TD]

[TD="align: right"]1/1/2013[/TD]
[TD="align: right"]12/31/2013[/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]A2[/TH]
[TD="align: left"]{=INDEX($F$2:$F$20,MATCH(1,(B2=$E$2:$E$20)*(C2>=$G$2:$G$20)*(C2<=$H$2:$H$20),0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[jbrosch]

Works GREAT in the formula evaluation window, but returns #NA in the field - any idea why?

 

[Weazel]

are you entering it as an array with Control Shift Enter ?

 

[jbrosch]

Copied formula to original work and got #N/A error. copied your sample data and replaced values in A with formula and got same error

 

[Weazel]

that's strange....I copied it out directly and its working for me.

....and Job title is in column A ? are the dates pasting as dates or text ?

 

[jbrosch]

Yes - verified the formula, in fact when I look at the field in the formula editor(clicking on fx), it shows returning the proper value - could I have a setting wrong? Any chance you can send your XLSX to JBrosch@Carolina.rr.com?