2nd to last value using match

[erickguz]

Hi,

Looking for a formula...

I have a columnG with names and the value that I want returned on column Y

BUT

I need the 2nd to last value. Is that possible?

For example,

COL G COLY
John Taylor apple
John Taylor carrot
John Taylor cucumber
John Taylor peach
John Taylor apricot
John Taylor cherry

I need "apricot", the 2nd to last value in COL G



Thanks

 

[Eric W]

Try:

=INDEX(Y:Y,LARGE(IF(G1:G6="John Taylor",ROW(G1:G6)),2))

confirmed with Control+Shift+Enter.

 

[erickguz]

Worked magically Eric - thanks for the magic.

 

[Eric W]

You're welcome.