Finding 2nd Occurrence

[erniepoe]

Hi All,

I have the following formula (#1) to find the 2nd occurrence in Column B when the date value in column D is greater than the date in cell F2, but i wanted to add a 2nd argument to it, If the value in column H equals the value of the cell in F3, but can't make it work (#2).

have tried different combinations with also adding the Row(H44:106) - ROW(INDEX(H44:H106) clause, but nothing seems to work for me.

Any ideas what I am doing wrong? Thanks so much for any help.

Best,
Ernie


1) =INDEX(B44:B106,SMALL(IF(D44:D106>F2,ROW(D44:D106)-ROW(INDEX(D44:D106,1,1))+1),2))


2) =INDEX(B44:B106,SMALL(IF(AND(D44:D106>F2,H44:H106=F3),ROW(D44:D106)-ROW(INDEX(D44:D106,1,1))+1),2))

 

[Fluff]

How about

=INDEX(FILTER(B44:B106,(D44:D106>F2)*(H44:H106=F3)),2)

 

[erniepoe]

Wow Fluff, that was way easier than I was making it to be. Thank you!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[erniepoe]

Fluff, is there a reason the formula doesn't work when you are referencing from another sheet in the same workbook? And are there alterations I can make to get it functional?

It turned into the following, but I am now getting a #CALC! error. Does it have to be performed on the same sheet?


=INDEX(FILTER('Unsecured Senior Debt'!B44:B106,('Unsecured Senior Debt'!D44:D106>BW14)*('Unsecured Senior Debt'!H44:H106=BW3)),2)

 

[Fluff]

Does it have to be performed on the same sheet?

Nope.

but I am now getting a #CALC! error.

That suggests there are no rows that match your criteria.

 

[erniepoe]

Oh oops, you are correct. BW14 needed to be BW13, so my self-esteem isn't at its highest right now

But thank you so much for your help!

 

[Fluff]

Glad to help.