Return a value if a for the 5th latest date for a corresponding critera (partially solved)

[npr00]

I am having a problem with the returning a value part of this formula.

My data is set up like so (dashes included to make it appear like columns in the post):

RR.UnitType----RR.NetContractedRent----RR.LeaseStart
a--------------100----------------------1/1/13
b--------------200----------------------1/2/13
c--------------300----------------------1/3/13
a--------------400----------------------1/4/13
b--------------500----------------------1/5/13
c--------------600----------------------1/6/13


So far I have the formula to pull back the last 5 dates for each unit type (this is the partially solved part):
{=LARGE(IF(RR.UnitType=$A$4,RR.LeaseStart),5)} <-this being the 5th latest date

$A$4 = the unit type I want to retrieve

Now my problem is finding the Net Contracted Rent that corresponds to that specific unit type and date. It should be noted that there could be multiple unit type "A"s with the same lease start date.

Any ideas out there?

Thank you in advance.

[ChemistB]

Now my problem is finding the Net Contracted Rent that corresponds to that specific unit type and date. It should be noted that there could be multiple unit type "A"s with the same lease start date.

So what you're saying is that the 5th, 4th and 3rd highest dates (for example) might all be the same?

It might help if you uploaded a sample spreadsheet which showed how your data and your results are laid out.

[npr00]

Yes, in one criteria the 5th and 4th highest dates are the same, and the 1st and 2nd highest dates are the same.

[ChemistB]

It'll be something like this

=INDEX($B$2:$B$1000,IF(LARGE(IF(RR.UnitType=$A$4,RR.LeaseStart),ROW(A1)),ROW($A$2:$A$1000)-1,0))
Arrayed function

[npr00]

Excel.Forum.Question.xlsx

I am still having an issue, it is just pulling back the first net contracted rent in the list

I have attached a file, hopefully this will be easier to understand!

Data is in columns A,B,C (E can be ignored). I have my list of criteria in K1:K8. I have the original formulas I used in K9:K13. I have the formula I used (it is slightly different what what was posted as I was playing around with it some) in cell N9.

[bebo021999]

Use helper column (column F) to add a very small different to cell with same date:
F2=C2+COUNTIF($C$1:C2,C2)/1000
In K9:

Please Login or Register  to view this content.

Confirmed with Ctrl-Shift-Enter
In N9:

Please Login or Register  to view this content.

Confirmed with Ctrl-Shift-Enter
Drag both down

[npr00]

That works great, thank you very much!

One further question, on the file I am actually applying this formula to the data starts on row 62, I tried putting 'sheet xyz'!B62 as well as 'sheet xyz'!B1, where you have A1 in the first formula, but none seem to work.

[bebo021999]

Keep A1 in your formula.
Instead of typing 1 for first row of formula, 2 for second row, I use row(A1)=1 for first row then drag down to get 2,3,...automatically
Therefore, row(A1) is used in general.

[npr00]

Ah, so simple and elegant, hah!

Thank you very much for all you assistance!