Return nth value in multi criteria vlookup

Question

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.

Make sure to provide input and expected output as plain text table in the question. Check my answer or other options to create a table easily, which are easy to copy/paste. Avoid sharing links like spreadsheets, which make the question useless for others or images, which are hard to copy. Also, note that your email address can also be accessed by the public, if you share Google files. — TheMaster
– TheMaster, Commented Mar 31 at 16:10

z.. · Accepted Answer · 2025-03-25 14:27:30Z

0

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

answered Mar 25 at 14:27

z..

14k2 gold badges10 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rockinfreakshow · Accepted Answer · 2025-03-25 15:38:54Z

0

Here's one approach you may test out (*expected output is differing in one scenario):

=map(A2:A,lambda(Σ,if(Σ="",,chooserows(filter(DATA!A:A,DATA!B:B=Σ,not(DATA!C:C)),countif(A2:Σ,Σ)))))

answered Mar 25 at 15:38

rockinfreakshow

30.7k4 gold badges19 silver badges30 bronze badges

Collectives™ on Stack Overflow

Return nth value in multi criteria vlookup

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related