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!

player0 · Accepted Answer · 2022-05-19 21:57:31Z

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, ))))

answered May 19, 2022 at 21:57

player0

1

Thank you @player0 !!! That works perfectly. I hadn't even considered using FILTER inside the ARRAYFORMULA, but I guess it works here because ARRAYFORMULA is referencing the VLOOKUP and not FILTER directly? Either way, the important thing is that it works... thank you again!
– confuseddesk
Commented May 20, 2022 at 16:03
@confuseddesk yes, AF is there for IF and VLOOKUP
– player0
Commented May 20, 2022 at 17:37

Add a comment |

Collectives™ on Stack Overflow

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

1 Answer 1

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Related