Index Large Match

[normanbox]

Hello All,

I'm sure this has been answered a thousand times, but I need some help. I'm using an index match formula and believe I need to embed a "large" function within it. For example in sheet 2, I have a formula in E1 that references columns A-C.

Column A Column B
ABC 12/31/2015
ABC 12/31/2014
ABC 12/31/2013
BCA 12/31/2016
BCA 06/30/2015
BCA 06/30/2014

I am looking for a formula that looks up the second date. If in cell D1, I write ABC, then ultimately I need the result of 12/31/2014 in cell E1. If in cell D2 I write BCA, then I need the result of 06/30/2015. I tried an index/match formula, but that didn't work. Basically it looked like this, index(Column B, Match(D1,Column A,0). I tried putting a "Large" formula in place of Column B within the index/match formula, but it gave me an error.

Any thoughts?

[FDibbins]

Looks like you are trying to find the 2nd largest date?

[avk]

Try with in "E1"

Please Login or Register  to view this content.

If your name "A1:A6" & date "B1:B6"
Criteria in "D1" as "ABC" or "BCA"

[philaugust2004]

Try this if all you need is the 2nd data for any value
=INDEX($B$1:$B$6,MATCH($A$10,$A$1:$A$6,0)+1)