How to return nth value from one Column based on another Column using ArrayFormula or QUERY?

Question

I'm working with a long, growing list of ratings data, and I'm trying to find a way to reference the 2nd (3rd, 4th, etc.) rating.

Here's a link to the sheet I'm working on.

The data I want to reference is in F5:H. I'm using QUERY to show most of the info I want (see A5 in my sheet for this formula), but where I'm running into trouble is when I try to retrieve only the Nth rating from Column H for a particular client based on the Client in Column A.

I'm sure I'm missing something obvious... the biggest (potential) limitation is that I need a formula that I can put in D5 that auto-fills this info for the entire column.

Thanks in advance!

Answer 1

try:

=ARRAYFORMULA(IF(A5:A="",, IFNA(VLOOKUP(A5:A, 
 FILTER(G5:H, COUNTIFS(G5:G, G5:G, ROW(G5:G), "<="&ROW(G5:G))=2), 2, ), 
 VLOOKUP(A5:A, G5:H, 2, ))))