google-sheetsgoogle-sheets-formulavlookuplookup

Return nth value in multi criteria vlookup


https://docs.google.com/spreadsheets/d/1jb9dKFAEL2IWP5C1z5S3cEKol27vYqcsISh1l16KlLQ/edit?usp=sharing

I have created the above Google sheet where I am doing a Vlookup in col. A to return Vlookup value in col. B (I have an additional condition that in the Dataset tab, lookup col. C to ensure it is FALSE).

In my vlookup result, I am only getting the 1st value. I need to get the 2nd - nth value.

Any help will be greatly appreciated.


Solution

  • You can label the search keys and the lookup column based on how many times the values occur then run a normal lookup.

    This formula in row 2 will populate the values for the entire column:

    =ARRAYFORMULA(LET(
       LABEL, LAMBDA(x, LET(
         r, SEQUENCE(ROWS(x)),
         x & 0 & COUNTIFS(x, x, r, "<" & r)
       )),
       XLOOKUP(LABEL(A2:A), LABEL(DATA!B:B), DATA!A:A, )
     ))