How To Lookup The 2nd last matching value?

[PaddyP]

Hi All,

I used this example from google to find out how to return the last matching value from a list but I want to return the second last value from the list instead and I am not sure what adjustment I need to do.

What formula can I enter in E2 so that the value returns 65 instead?

Many thanks!

[JohnTopley]

try

=INDEX($B$2:$B$8,AGGREGATE(15,6,ROW(A1:A8)/($A$2:$A$8=$D$2),COUNTIF($A$2:A$8,D$2)-1))

[TMS]

@John: works ok when there are two or more options to choose from but fails if there is only one option. But easy to fix with an IFERROR( … , INDEX/MATCH( … ))

Well done though, I wouldn't know where to start with this problem/solution.

[PaddyP]

Thanks for the solution John.

My real data is actually on two different sheets and has two blank cells above the list. See new template.

Can you help me adjust the formula given the new cell locations please?

[TMS]

Try:

Please Login or Register  to view this content.

[TMS]

With the revision …

Please Login or Register  to view this content.

[PaddyP]

Thanks but I moved the ranges around so this isn't working.

[JohnTopley]

fyi; with only one entry

=INDEX($B$2:$B$8,AGGREGATE(15,6,ROW(A1:A8)/($A$2:$A$8=$D$2),MAX(COUNTIF($A$2:A$8,D$2)-1,1)))

[JohnTopley]

try

=INDEX(Sheet1!$F$4:$F$100,AGGREGATE(15,6,(ROW($A$4:$A$100)-ROW($A$4)+1)/(Sheet1!$E$4:$E$100=$A$2),MAX(COUNTIF(Sheet1!$E$4:$E$100,$A$2)-1,1)))

[PaddyP]

John this last revision works to perfection. Thank you!

[JohnTopley]

You're welcome and thank you for the rep.

[wk9128]

Ans Post#1
Cell E2 formula

Ans Post#4
worksheet name : Sheet2
Cell D2 formula