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.

https://docs.google/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.

• google-sheets
• google-sheets-formula
• vlookup
• lookup

Share Improve this question Follow edited Mar 25 at 15:13 marc_s 756k184184 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Mar 25 at 14:01 user30056942user30056942 322 bronze badges 1

• 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 Commented Mar 31 at 16:10

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 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, )
 ))

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