multiple date look up from different rows from two tabs

M

mcmahon_66

New Member
Joined
May 2, 2019
Messages
5
I have been trying to figure out how to apply my expected result using a separate tab lookup. The section to the left is what I am trying to populate the expected result from the section on the right. I need to match the agent columns together then plug the tax treatment in the expected result based on the date range.(I currently plugged that in to show what I am trying to do.

Thank You for any assistance




agent ContractEFF_DTXPR_DTexpected tax resultagentTax treatmentEFF_DTXPR_DT
000105Contract 111/22/19923/31/1992109900010510991/22/19923/31/1992
000105Contract 124/1/1992 8/30/1992 w2000105W-24/1/19921/1/1999
000105Contract 138/31/1992 8/13/1995 w200010510991/2/19992/10/2014
000105Contract 141/4/19938/13/1995 w2
000105Contract 158/14/19951/1/1999 w2
000105Contract 168/14/19951/1/1999 w2
000105Contract 171/2/199912/28/20071099
000105Contract 181/2/199912/28/20071099
000105Contract 1912/29/20072/10/20141099

<tbody>
</tbody>



<tbody>
</tbody>
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
If you have Excel 2010 or later try:

Excel Workbook
ABCDE
1agentContractEFF_DTXPR_DTexpected tax result
2105Contract 111/22/19923/31/19921099
3105Contract 124/1/19928/30/1992W-2
4105Contract 138/31/19928/13/1995W-2
5105Contract 141/4/19938/13/1995W-2
6105Contract 158/14/19951/1/1999W-2
7105Contract 168/14/19951/1/1999W-2
8105Contract 171/2/199912/28/20071099
9105Contract 181/2/199912/28/20071099
10105Contract 1912/29/20072/10/20141099
Sheet 1
Excel Workbook
ABCD
1agentTax treatmentEFF_DTXPR_DT
210510991/22/19923/31/1992
3105W-24/1/19921/1/1999
410510991/2/19992/10/2014
Sheet 2
 
Upvote 0
Thank you, I have never used the iferrors or index before. I am going to try and apply this to my entire spreadsheet withe the extended field ranges.
 
Upvote 0
Thank you AhoyNC,
I tried extending the formula and I am getting no match it is failing at section ($A18=Sheet2!$A$2:$A$23) in my formula. If I apply each agent separately, it works fine. I am newer to these formulas and cannot figure out why its failing.

Sheet 1

agentContractEFF_DTXPR_DTexpected tax result
000105Contract 111/22/19923/31/19921099
000105Contract 124/1/19928/30/1992W-2
000105Contract 138/31/19928/13/1995W-2
000105Contract 141/4/19938/13/1995W-2
000105Contract 158/14/19951/1/1999W-2
000105Contract 168/14/19951/1/1999W-2
000105Contract 171/2/199912/28/20071099
000105Contract 181/2/199912/28/20071099
000105Contract 1912/29/20072/10/20141099
000275Contract 201/6/199212/28/2007W-2
000275Contract 212/24/199212/28/2007W-2
000275Contract 2212/29/200712/31/2013W-2
000275Contract 231/1/20141/2/20141099
000275Contract 241/3/20148/3/20141099
000307Contract 251/1/19501/23/19921099
000307Contract 261/24/19924/19/19921099
000307Contract 274/20/199212/5/2003No Match
000307Contract 287/6/199212/5/2003No Match
000307Contract 292/17/200412/28/20071099
000307Contract 302/17/200412/28/20071099
000307Contract 3112/29/20072/10/20141099
000417Contract 321/1/19501/19/19921099
000417Contract 331/20/19921/23/1994No Match
000417Contract 349/17/19921/23/1994No Match
000417Contract 351/24/199412/28/2007No Match
000417Contract 361/24/199412/28/2007No Match
000417Contract 3712/29/20073/31/20081099
000417Contract 384/1/20081/3/2015No Match
000417Contract 391/4/201512/31/99991099

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


Spreadsheet formula sheet 1
CellFormula
E18=IFERROR(INDEX(Sheet2!$B$2:$B$23,AGGREGATE(15,6,(ROW(Sheet2!$B$2:$B$23)-ROW(Sheet2!$B$2)+1)/(($A18=Sheet2!$A$2:$A$23)*($C18>=Sheet2!$C$2:$C$23)*($D18<=Sheet2!$D$2:$D$23)),1)),"No Match")

<tbody>
</tbody>

Sheet 2
agentTax treatmentEFF_DTXPR_DT
00010510991/22/19923/31/1992
000105W-24/1/19921/1/1999
00010510991/2/19992/10/2014
000275W-21/6/199212/31/2013
00027510991/1/20141/2/2014
00027510991/3/20148/3/2014
00030710991/1/19501/23/1992
00030710991/24/19924/19/1992
000307W-24/20/19922/27/1995
00030710992/28/19955/31/1995
000307W-26/1/199512/5/2003
000307109912/6/200312/17/2003
00030710992/17/20042/10/2014
00041710991/1/19501/19/1992
000417W-21/20/199212/31/1993
000417W-21/1/19948/31/1997
000417W-29/1/199712/28/2007
000417109912/29/20073/31/2008
000417W-24/1/20088/16/2010
000417W-28/17/20101/1/2013
000417W-21/2/20131/3/2015
00041710991/4/201512/31/9999

<colgroup><col span="2"><col span="2"></colgroup><tbody>
</tbody>
 
Upvote 0
Maybe I'm missing something, but on the section A18 I don't see a date range that matches.
Three dates would fall in the EFF_DT range and three fall in the XPR_DT range, but I don't see anything for agent 307 that meets both criteria.

Excel Workbook
ABCDE
1agentContractEFF_DTXPR_DTexpected tax result
16307Contract 251/1/19501/23/19921099
17307Contract 261/24/19924/19/19921099
18307Contract 274/20/199212/5/2003No Match
19307Contract 287/6/199212/5/2003No Match
20307Contract 292/17/200412/28/20071099
21307Contract 302/17/200412/28/20071099
22307Contract 3112/29/20072/10/20141099
Sheet 1
Excel Workbook
ABCD
1agentTax treatmentEFF_DTXPR_DT
830710991/1/19501/23/1992
930710991/24/19924/19/1992
10307W-24/20/19922/27/1995
1130710992/28/19955/31/1995
12307W-26/1/199512/5/2003
13307109912/6/200312/17/2003
1430710992/17/20042/10/2014
Sheet 2
 
Upvote 0
E2==IFERROR(INDEX(Sheet2!$B$2:$B$23,AGGREGATE(15,6,(ROW(Sheet2!$B$2:$B$23)-ROW(Sheet2!$B$2)+1)/(($A18=Sheet2!$A$2:$A$23)*($C18>=Sheet2!$C$2:$C$23)*($D18<=Sheet2!$D$2:$D$23)),1)),"No Match")





AhoyNC said:
Maybe I'm missing something, but on the section A18 I don't see a date range that matches.
Three dates would fall in the EFF_DT range and three fall in the XPR_DT range, but I don't see anything for agent 307 that meets both criteria.

Sheet 1

ABCDE
1agentContractEFF_DTXPR_DTexpected tax result
16307Contract 251/1/19501/23/19921099
17307Contract 261/24/19924/19/19921099
18307Contract 274/20/199212/5/2003No Match
19307Contract 287/6/199212/5/2003No Match
20307Contract 292/17/200412/28/20071099
21307Contract 302/17/200412/28/20071099
22307Contract 3112/29/20072/10/20141099

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:75px;"><col style="width:81px;"><col style="width:128px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas
CellFormula
E18=IFERROR(INDEX(Sheet2!$B$2:$B$23,AGGREGATE(15,6,(ROW(Sheet2!$B$2:$B$23)-ROW(Sheet2!$B$2)+1)/(($A18=Sheet2!$A$2:$A$23)*($C18>=Sheet2!$C$2:$C$23)*($D18<=Sheet2!$D$2:$D$23)),1)),"No Match")

<tbody>
</tbody>

<tbody>
</tbody>


Sheet 2

ABCD
1agentTax treatmentEFF_DTXPR_DT
830710991/1/19501/23/1992
930710991/24/19924/19/1992
10307W-24/20/19922/27/1995
1130710992/28/19955/31/1995
12307W-26/1/199512/5/2003
13307109912/6/200312/17/2003
1430710992/17/20042/10/2014

<colgroup><col style="width:30px; "><col style="width:77px;"><col style="width:99px;"><col style="width:99px;"><col style="width:108px;"></colgroup><tbody>
</tbody>


Excel tables to the web >> Excel Jeanie HTML 4
Click to expand...
 
Upvote 0
What is the expected answer for row 18 where EFF-DT = 4/20/1992 and XPR-DT = 12/5/2003?
 
Upvote 0
AhoyNC said:
What is the expected answer for row 18 where EFF-DT = 4/20/1992 and XPR-DT = 12/5/2003?
Click to expand...

I am guessing it's not possible. My contracts and my tax treatments do not line up. In reality, I need to make the 2 contract rows into three rows based on tax treatment. The results I really need are the ones in bold below.

thank you for your help


agentContractEFF_DTXPR_DTexpected tax result
000307Legacy FY 1/1/19501/23/19921099
000307Legacy FY 1/24/19924/19/19921099
000307Legacy FY 4/20/199212/5/2003No Match
000307Legacy FY 7/6/199212/5/2003No Match
000307Legacy FY 2/17/200412/28/20071099
000307Legacy FY 2/17/200412/28/20071099
000307GDC FY, RN 12/29/20072/10/20141099

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


agentTax treatmentEFF_DTXPR_DT
00030710991/1/19501/23/1992
00030710991/24/19924/19/1992
000307W-24/20/19922/27/1995
00030710992/28/19955/31/1995
000307W-26/1/199512/5/2003
000307109912/6/200312/17/2003
00030710992/17/20042/10/2014

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Similar threads

skyh3ck
  • Question
1st match column header, 2nd lookup a value in same column, 3rd return corresponding value from the very first column ?? need help
Replies
2
Views
262
skyh3ck
skyh3ck
F
  • Question
Enhance simply subtract of date/times of time spent, to including indexing and subtracting time blocked from another table (with multiple criteria)
Replies
0
Views
216
freeb1893
F
S
  • Solved
How to separate text out to a new column - Text to Column does not work
Replies
7
Views
546
kevin9999
K
F
  • Question
Looking to index some data based on multiple criteria, then index further data based on the 1st indexed data results
2
Replies
13
Views
485
KRice
K
C
  • Question
Suggest a pricing matrix for Adult/Concession/Junior ‘Flexi Season Ticket’.
Replies
2
Views
354
Chuky
C

Forum statistics

Threads
1,214,553
Messages
6,120,176
Members
448,948
Latest member
spamiki

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top