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




[TABLE="width: 1029"]
<tbody>[TR]
[TD]agent[/TD]
[TD] Contract[/TD]
[TD]EFF_DT[/TD]
[TD]XPR_DT[/TD]
[TD]expected tax result[/TD]
[TD][/TD]
[TD]agent[/TD]
[TD]Tax treatment[/TD]
[TD]EFF_DT[/TD]
[TD]XPR_DT[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 11[/TD]
[TD="align: right"]1/22/1992[/TD]
[TD="align: right"]3/31/1992[/TD]
[TD="align: right"]1099[/TD]
[TD][/TD]
[TD]000105[/TD]
[TD="align: right"]1099[/TD]
[TD="align: right"]1/22/1992[/TD]
[TD="align: right"]3/31/1992[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 12[/TD]
[TD="align: right"]4/1/1992[/TD]
[TD="align: right"] 8/30/1992[/TD]
[TD] w2[/TD]
[TD][/TD]
[TD]000105[/TD]
[TD]W-2[/TD]
[TD="align: right"]4/1/1992[/TD]
[TD="align: right"]1/1/1999[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 13[/TD]
[TD="align: right"]8/31/1992[/TD]
[TD="align: right"] 8/13/1995[/TD]
[TD] w2[/TD]
[TD][/TD]
[TD]000105[/TD]
[TD="align: right"]1099[/TD]
[TD="align: right"]1/2/1999[/TD]
[TD="align: right"]2/10/2014[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 14[/TD]
[TD="align: right"]1/4/1993[/TD]
[TD="align: right"]8/13/1995[/TD]
[TD] w2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 15[/TD]
[TD="align: right"]8/14/1995[/TD]
[TD="align: right"]1/1/1999[/TD]
[TD] w2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 16[/TD]
[TD="align: right"]8/14/1995[/TD]
[TD="align: right"]1/1/1999[/TD]
[TD] w2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 17[/TD]
[TD="align: right"]1/2/1999[/TD]
[TD="align: right"]12/28/2007[/TD]
[TD="align: right"]1099[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 18[/TD]
[TD="align: right"]1/2/1999[/TD]
[TD="align: right"]12/28/2007[/TD]
[TD="align: right"]1099[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 19[/TD]
[TD="align: right"]12/29/2007[/TD]
[TD="align: right"]2/10/2014[/TD]
[TD="align: right"]1099[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1029"]
<tbody>[TR]
[TD]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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

[TABLE="class: grid, width: 435"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]agent[/TD]
[TD]Contract[/TD]
[TD]EFF_DT[/TD]
[TD]XPR_DT[/TD]
[TD]expected tax result[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 11[/TD]
[TD]1/22/1992[/TD]
[TD]3/31/1992[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 12[/TD]
[TD]4/1/1992[/TD]
[TD]8/30/1992[/TD]
[TD]W-2[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 13[/TD]
[TD]8/31/1992[/TD]
[TD]8/13/1995[/TD]
[TD]W-2[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 14[/TD]
[TD]1/4/1993[/TD]
[TD]8/13/1995[/TD]
[TD]W-2[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 15[/TD]
[TD]8/14/1995[/TD]
[TD]1/1/1999[/TD]
[TD]W-2[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 16[/TD]
[TD]8/14/1995[/TD]
[TD]1/1/1999[/TD]
[TD]W-2[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 17[/TD]
[TD]1/2/1999[/TD]
[TD]12/28/2007[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 18[/TD]
[TD]1/2/1999[/TD]
[TD]12/28/2007[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]Contract 19[/TD]
[TD]12/29/2007[/TD]
[TD]2/10/2014[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000275[/TD]
[TD]Contract 20[/TD]
[TD]1/6/1992[/TD]
[TD]12/28/2007[/TD]
[TD]W-2[/TD]
[/TR]
[TR]
[TD]000275[/TD]
[TD]Contract 21[/TD]
[TD]2/24/1992[/TD]
[TD]12/28/2007[/TD]
[TD]W-2[/TD]
[/TR]
[TR]
[TD]000275[/TD]
[TD]Contract 22[/TD]
[TD]12/29/2007[/TD]
[TD]12/31/2013[/TD]
[TD]W-2[/TD]
[/TR]
[TR]
[TD]000275[/TD]
[TD]Contract 23[/TD]
[TD]1/1/2014[/TD]
[TD]1/2/2014[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000275[/TD]
[TD]Contract 24[/TD]
[TD]1/3/2014[/TD]
[TD]8/3/2014[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Contract 25[/TD]
[TD]1/1/1950[/TD]
[TD]1/23/1992[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Contract 26[/TD]
[TD]1/24/1992[/TD]
[TD]4/19/1992[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Contract 27[/TD]
[TD]4/20/1992[/TD]
[TD]12/5/2003[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Contract 28[/TD]
[TD]7/6/1992[/TD]
[TD]12/5/2003[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Contract 29[/TD]
[TD]2/17/2004[/TD]
[TD]12/28/2007[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Contract 30[/TD]
[TD]2/17/2004[/TD]
[TD]12/28/2007[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Contract 31[/TD]
[TD]12/29/2007[/TD]
[TD]2/10/2014[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]Contract 32[/TD]
[TD]1/1/1950[/TD]
[TD]1/19/1992[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]Contract 33[/TD]
[TD]1/20/1992[/TD]
[TD]1/23/1994[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]Contract 34[/TD]
[TD]9/17/1992[/TD]
[TD]1/23/1994[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]Contract 35[/TD]
[TD]1/24/1994[/TD]
[TD]12/28/2007[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]Contract 36[/TD]
[TD]1/24/1994[/TD]
[TD]12/28/2007[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]Contract 37[/TD]
[TD]12/29/2007[/TD]
[TD]3/31/2008[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]Contract 38[/TD]
[TD]4/1/2008[/TD]
[TD]1/3/2015[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]Contract 39[/TD]
[TD]1/4/2015[/TD]
[TD]12/31/9999[/TD]
[TD]1099[/TD]
[/TR]
</tbody>[/TABLE]


Spreadsheet formula sheet 1
[TABLE="class: grid, width: 1000"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]E18[/TD]
[TD]=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")[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
[TABLE="class: grid, width: 278"]
<colgroup><col span="2"><col span="2"></colgroup><tbody>[TR]
[TD]agent[/TD]
[TD]Tax treatment[/TD]
[TD]EFF_DT[/TD]
[TD]XPR_DT[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]1099[/TD]
[TD="align: right"]1/22/1992[/TD]
[TD="align: right"]3/31/1992[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]W-2[/TD]
[TD="align: right"]4/1/1992[/TD]
[TD="align: right"]1/1/1999[/TD]
[/TR]
[TR]
[TD]000105[/TD]
[TD]1099[/TD]
[TD="align: right"]1/2/1999[/TD]
[TD="align: right"]2/10/2014[/TD]
[/TR]
[TR]
[TD]000275[/TD]
[TD]W-2[/TD]
[TD="align: right"]1/6/1992[/TD]
[TD="align: right"]12/31/2013[/TD]
[/TR]
[TR]
[TD]000275[/TD]
[TD]1099[/TD]
[TD="align: right"]1/1/2014[/TD]
[TD="align: right"]1/2/2014[/TD]
[/TR]
[TR]
[TD]000275[/TD]
[TD]1099[/TD]
[TD="align: right"]1/3/2014[/TD]
[TD="align: right"]8/3/2014[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]1099[/TD]
[TD="align: right"]1/1/1950[/TD]
[TD="align: right"]1/23/1992[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]1099[/TD]
[TD="align: right"]1/24/1992[/TD]
[TD="align: right"]4/19/1992[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]W-2[/TD]
[TD="align: right"]4/20/1992[/TD]
[TD="align: right"]2/27/1995[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]1099[/TD]
[TD="align: right"]2/28/1995[/TD]
[TD="align: right"]5/31/1995[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]W-2[/TD]
[TD="align: right"]6/1/1995[/TD]
[TD="align: right"]12/5/2003[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]1099[/TD]
[TD="align: right"]12/6/2003[/TD]
[TD="align: right"]12/17/2003[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]1099[/TD]
[TD="align: right"]2/17/2004[/TD]
[TD="align: right"]2/10/2014[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]1099[/TD]
[TD="align: right"]1/1/1950[/TD]
[TD="align: right"]1/19/1992[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]W-2[/TD]
[TD="align: right"]1/20/1992[/TD]
[TD="align: right"]12/31/1993[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]W-2[/TD]
[TD="align: right"]1/1/1994[/TD]
[TD="align: right"]8/31/1997[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]W-2[/TD]
[TD="align: right"]9/1/1997[/TD]
[TD="align: right"]12/28/2007[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]1099[/TD]
[TD="align: right"]12/29/2007[/TD]
[TD="align: right"]3/31/2008[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]W-2[/TD]
[TD="align: right"]4/1/2008[/TD]
[TD="align: right"]8/16/2010[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]W-2[/TD]
[TD="align: right"]8/17/2010[/TD]
[TD="align: right"]1/1/2013[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]W-2[/TD]
[TD="align: right"]1/2/2013[/TD]
[TD="align: right"]1/3/2015[/TD]
[/TR]
[TR]
[TD]000417[/TD]
[TD]1099[/TD]
[TD="align: right"]1/4/2015[/TD]
[TD="align: right"]12/31/9999[/TD]
[/TR]
</tbody>[/TABLE]
 
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
Contract 25
Contract 26
Contract 28No Match
Contract 29
Contract 30
Contract 31

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]agent[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Contract[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]EFF_DT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]XPR_DT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]expected tax result[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]16[/TD]
[TD="align: center"]307[/TD]

[TD="align: right"]1/1/1950[/TD]
[TD="align: right"]1/23/1992[/TD]
[TD="align: right"]1099[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]17[/TD]
[TD="align: center"]307[/TD]

[TD="align: right"]1/24/1992[/TD]
[TD="align: right"]4/19/1992[/TD]
[TD="align: right"]1099[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]18[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]307[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]Contract 27[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]4/20/1992[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]12/5/2003[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] "]No Match[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]19[/TD]
[TD="align: center"]307[/TD]

[TD="align: right"]7/6/1992[/TD]
[TD="align: right"]12/5/2003[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]20[/TD]
[TD="align: center"]307[/TD]

[TD="align: right"]2/17/2004[/TD]
[TD="align: right"]12/28/2007[/TD]
[TD="align: right"]1099[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]21[/TD]
[TD="align: center"]307[/TD]

[TD="align: right"]2/17/2004[/TD]
[TD="align: right"]12/28/2007[/TD]
[TD="align: right"]1099[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]22[/TD]
[TD="align: center"]307[/TD]

[TD="align: right"]12/29/2007[/TD]
[TD="align: right"]2/10/2014[/TD]
[TD="align: right"]1099[/TD]

</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
W-2
W-2

<colgroup><col style="width:30px; "><col style="width:77px;"><col style="width:99px;"><col style="width:99px;"><col style="width:108px;"></colgroup><tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]agent[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]Tax treatment[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]EFF_DT[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=c0c0c0]#c0c0c0[/URL] , align: center"]XPR_DT[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]307[/TD]
[TD="align: right"]1099[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]1/1/1950[/TD]
[TD="align: right"]1/23/1992[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]307[/TD]
[TD="align: right"]1099[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]1/24/1992[/TD]
[TD="align: right"]4/19/1992[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]
[TD="align: center"]307[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]4/20/1992[/TD]
[TD="align: right"]2/27/1995[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]
[TD="align: center"]307[/TD]
[TD="align: right"]1099[/TD]
[TD="align: right"]2/28/1995[/TD]
[TD="align: right"]5/31/1995[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]12[/TD]
[TD="align: center"]307[/TD]

[TD="align: right"]6/1/1995[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]12/5/2003[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]13[/TD]
[TD="align: center"]307[/TD]
[TD="align: right"]1099[/TD]
[TD="align: right"]12/6/2003[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]12/17/2003[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]14[/TD]
[TD="align: center"]307[/TD]
[TD="align: right"]1099[/TD]
[TD="align: right"]2/17/2004[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: right"]2/10/2014[/TD]

</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


[TABLE="class: grid, width: 481"]
<colgroup><col><col><col span="2"><col></colgroup><tbody>[TR]
[TD]agent[/TD]
[TD]Contract[/TD]
[TD]EFF_DT[/TD]
[TD]XPR_DT[/TD]
[TD]expected tax result[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Legacy FY [/TD]
[TD]1/1/1950[/TD]
[TD]1/23/1992[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Legacy FY [/TD]
[TD]1/24/1992[/TD]
[TD]4/19/1992[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Legacy FY [/TD]
[TD]4/20/1992[/TD]
[TD]12/5/2003[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Legacy FY [/TD]
[TD]7/6/1992[/TD]
[TD]12/5/2003[/TD]
[TD]No Match[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Legacy FY [/TD]
[TD]2/17/2004[/TD]
[TD]12/28/2007[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]Legacy FY [/TD]
[TD]2/17/2004[/TD]
[TD]12/28/2007[/TD]
[TD]1099[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]GDC FY, RN [/TD]
[TD]12/29/2007[/TD]
[TD]2/10/2014[/TD]
[TD]1099[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 515"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]agent[/TD]
[TD]Tax treatment[/TD]
[TD]EFF_DT[/TD]
[TD]XPR_DT[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]1099[/TD]
[TD="align: right"]1/1/1950[/TD]
[TD="align: right"]1/23/1992[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]1099[/TD]
[TD="align: right"]1/24/1992[/TD]
[TD="align: right"]4/19/1992[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]W-2[/TD]
[TD="align: right"]4/20/1992[/TD]
[TD="align: right"]2/27/1995[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]1099[/TD]
[TD="align: right"]2/28/1995[/TD]
[TD="align: right"]5/31/1995[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]W-2[/TD]
[TD="align: right"]6/1/1995[/TD]
[TD="align: right"]12/5/2003[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]1099[/TD]
[TD="align: right"]12/6/2003[/TD]
[TD="align: right"]12/17/2003[/TD]
[/TR]
[TR]
[TD]000307[/TD]
[TD]1099[/TD]
[TD="align: right"]2/17/2004[/TD]
[TD="align: right"]2/10/2014[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Multiple Search Criteria in Excel Table
Replies
4
Views
127
KOKOSEK
KOKOSEK
U
  • Solved
SumIf formula with multiple criteria from different tab
Replies
3
Views
489
Alex Blakenburg
A
skyh3ck
  • Question 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
546
skyh3ck
skyh3ck
F
  • Question 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
393
freeb1893
F
S
  • Solved
How to separate text out to a new column - Text to Column does not work
Replies
7
Views
694
kevin9999
K

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

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