Returning a value based on multiple criteria

[dedelkoo]

Good Morning All;

I'm sorry if this is covered in another post but frankly I could think of how to describe what I'm trying to accomplish (or that its practicibly possible for that matter).

Below is a data set that I'm trying to query. Essentially each investor is separated into their own accounts; further to that, the investment is broken down by the date that the investor bought in. Each time the investor gives more money they are given a separate "lot" which is tracked. However the investor only cares about their return based on their oldest lot, which is all that's reported to them.

Thus, I'm trying to craft a formulae that would be dropped into the green cells below that would determine:

1 - Are there any other lines with the same account number as this line?
2 - Of those lines, which one contains the oldest lot?
3 - What is the return % of the oldest lot?

The last complication is that in my working copy these columns are not side-by-side, there are multiple columns in between each of these data points that I've left of for ease of illustration.

The results in the green cells are the ones I desire; however currently they're just typed in manually in the book I work with. Is there a formula, or combination of several that could perform this task?

All thoughts are appreciated.

<TABLE style="WIDTH: 351pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=466><COLGROUP><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3108" span=4 width=85><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4608" width=126><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 64pt; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20 width=85>Name</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=85>Account #</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=85>Lot Date</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 64pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=85>Return %</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; WIDTH: 95pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" width=126>Reported Return %</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20>Mr. Smith</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" align=right>101</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 align=right>5/10/2009</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 align=right>5.04%</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 align=right>5.04%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20>Mr. Brown</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" align=right>102</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 align=right>6/30/2009</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 align=right>6.20%</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 align=right>6.20%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20>Mr. Brown</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" align=right>102</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 align=right>7/1/2010</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 align=right>5.08%</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 align=right>6.20%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20>Mr. Thom</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" align=right>103</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 align=right>5/10/2009</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 align=right>11.00%</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 align=right>11.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20>Mr. Thom</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" align=right>103</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 align=right>12/31/2009</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 align=right>6.45%</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 align=right>11.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20>Mr. Thom</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" align=right>103</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 align=right>5/31/2010</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 align=right>11.30%</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 align=right>11.00%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20>Mr. Bowles</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" align=right>104</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 align=right>3/1/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 align=right>1.09%</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 align=right>1.09%</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" height=20>Mr. Green</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" align=right>105</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl65 align=right>4/1/2011</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: transparent; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl66 align=right>3.50%</TD><TD style="BORDER-BOTTOM: #e0dfe3; BORDER-LEFT: #e0dfe3; BACKGROUND-COLOR: #d7e4bc; BORDER-TOP: #e0dfe3; BORDER-RIGHT: #e0dfe3" class=xl67 align=right>3.50%</TD></TR></TBODY></TABLE>

 

[Jerry Sullivan]

Hi dedelkoo,

If multiple lot dates for each account are sorted from Oldest to Newest lot date as shown in your example date, then you can use a Vlookup formula...

Excel Workbook
	A	B	C	D	E
1	Name	Account #	Lot Date	Return %	Reported Return %
2	Mr. Smith	101	5/10/2009	5.04%	5.04%
3	Mr. Brown	102	6/30/2009	6.20%	6.20%
4	Mr. Brown	102	7/1/2010	5.08%	6.20%
Sheet1

 

[dedelkoo]

Aha!

You're right; I was overcomplicating it. The list currently is not sorted but that's an easy fix. I was trying to bring a battleship to a knife fight it seems.

Thanks again,

Derek

 

[Jerry Sullivan]

You're right; I was overcomplicating it. The list currently is not sorted but that's an easy fix. I was trying to bring a battleship to a knife fight it seems.

Glad that was an easy fix for you.
Save that battleship for another day!