Using VLOOKUP to Find the Nth Occurrence in Google Sheets

The VLOOKUP function does not have a specific argument to look up the nth occurrence of a value in Google Sheets. However, this can be achieved with the support of other functions.

Certainly, there is a simpler approach using the QUERY function to find the nth occurrence of a value. Another option is the FILTER and CHOOSEROWS combo. However, these options have limitations in finding the nth occurrence of multiple values.

In this tutorial, I’ll explain how to use VLOOKUP to find the nth occurrence, as well as explore the mentioned alternatives.

Understanding Nth Occurrence in Vertical Lookup in Google Sheets

First of all, let me explain what the nth occurrence of a search key in a dataset is.

I have fruit names in column A, and the name “Mango” appears twice in cells A3 and A5. The second occurrence of “Mango” is the value in cell A5. I want to search for this value in column A and return another value from row #5 from a different column. This is called the nth occurrence in vertical lookup.

To vertically search for a value and return another value from another cell (column) in the same row, we usually use the VLOOKUP function. However, it has no built-in capability to find the nth occurrence.

QUERY to Find Nth Occurrence in Google Sheets

To find the second occurrence of “Mango” in column A and get the corresponding value from column B, you can use the following QUERY formula:

=QUERY(A1:B, "SELECT B WHERE A='Mango' LIMIT 1 OFFSET 1", 0)

If your data has a header row, you should use it as follows:

=QUERY(A1:B, "SELECT B WHERE A='Mango' LIMIT 1 OFFSET 1 LABEL B'' ", 1)

In this, note that the lookup value, i.e., “Mango,” is case-sensitive.

The formula finds the 2nd occurrence of “Mango” in column A by offsetting rows and limiting the result to 1 row from column B, while removing the header label in the output.

When you use these formulas, replace:

A1:B with your range to look up.
B in “SELECT B” with the result column ID.
A='Mango' with the lookup column ID and value.
B in “LABEL B” with the result column ID.
1 in “OFFSET 1” with n-1, i.e., to find the second occurrence specify 1, for the third occurrence specify 2, and so on.

Specify 1 or 0 in the last part of the formula based on whether you have a header row or not.

FILTER and CHOOSEROWS to Find Nth Occurrence in Google Sheets

This is a modern approach for nth value lookup in Google Sheets.

In this method, we will use the FILTER function to filter the result column based on the condition in the lookup column.

Then, use CHOOSEROWS to pick the row based on ‘n’.

=CHOOSEROWS(FILTER(B1:B, A1:A="Mango"), 2)

When you use this combination, replace B1:B with the result column range and A1:A with the lookup column range. Also, don’t forget to replace “Mango” with the search key and 2 with the occurrence number.

This formula is not case-sensitive and works regardless of whether your data has a header row or not.

Using VLOOKUP to Vertically Lookup the First, Second, or Third Occurrence

Though the QUERY and CHOOSEROWS & FILTER combo are easy to use, they have one issue in common: they may not be able to look up more than one search key at a time.

For example, if you want to search for both “Apple” and “Mango”, you might need to use the MAP lambda function for mapping each search key. This makes them complex and resource-intensive.

Here’s where VLOOKUP becomes noteworthy. Here’s how to use VLOOKUP to find the nth occurrence in Google Sheets.

In cell C1, enter the following formula to return the running count of values in column A, the lookup column:

=ArrayFormula(COUNTIFS(A1:A, A1:A, ROW(A1:A), "<="&ROW(A1:A)))

I assume the search keys are in E2:E3.

In cell F2, enter the following VLOOKUP formula:

=ArrayFormula(VLOOKUP(E2:E3&"~2", {A1:A&"~"&C1:C, B1:B}, 2, 0))

When using VLOOKUP, replace E2:E3 with the search key range, A1:A with the search column, and B1:B with the result column. Additionally, in ~2, replace 2 with the occurrence number you want.

Note: If you don’t want the helper column C, replace C1:C in the formula with the C1 formula.

This way, we can use VLOOKUP to find the nth occurrence in Google Sheets.

Formula Breakdown

The formula follows the syntax VLOOKUP(search_key, range, index, [is_sorted]).

Where:

search_key: E2:E3&"~2" – adds the occurrence number to the search keys delimited by "~". The character "~" is used as a delimiter with no specific meaning.
range: {A1:A&"~"&C1:C, B1:B} – a two-column range where the first column contains values in A1:A with the added occurrence number. The second column is B1:B.
index: 2 – the result column.
is_sorted: 0 – represents an exact match.
ArratFormula(…): The formula requires the ARRAYFORMULA function because it uses multiple search keys.

We have seen three of the best options to find the nth occurrence in Google Sheets. Among them, VLOOKUP is dynamic with its array output. In addition, we can use a combination of INDEX, MATCH, and SMALL for the same purpose, though without the array capability. You can find an example of that in my SMALL function guide.

Resources

32 COMMENTS

Jorge Soria August 10, 2023 At 1:25 am

Hello Prashanth,

Thank you very much. The formula is retrieving all the matches from the same search key. I have a quick question: Is there any way to arrange the records in the same row where the search key is found? It can expand to 10 columns (I have that layout already).

- Prashanth August 10, 2023 At 9:18 am
  
  Yes! You can use the TOCOL function to make the output into a single column. Then, you can use the WRAPCOLS function to wrap the result into n columns.
  
  You can see a similar approach in Excel here. However, if you use that approach (formula), you will need to remove the DROP function. This may leave one blank row at the top of your result, but it will not cause any issues.
  
  If you would like any further assistance, please share a sample file URL (with Viewer access) below.
  
Jorge Soria August 8, 2023 At 2:29 am

Hi Prashant,

I came across your tutorial and it’s really helpful in retrieving information from a dataset I have. However, I’m stuck in a loop where everything I try only gets me the first matching records of the dataset.

I have this formula:

=ArrayFormula(IF(ROW(U:U)=1, "DATOS", IF(ISBLANK('P. ALBA 2023'!B:B), "", IFERROR(VLOOKUP(B:B, 'P. ALBA 2022'!C:Z, {8, 3, 9, 11, 10}, FALSE), "*"))))

This formula is capable of retrieving data from several columns in the row where a match is found. However, I want to populate additional columns as there are additional matches in the same dataset.

For example, let’s say I have a name in my search sheet, and that same name is recorded twice in the database (the P. ALBA 2022 sheet). What I want to do is to retrieve the same columns {8, 3, 9, 11, 10} for each match.

Do you have any suggestions on how I can do this?

- Prashanth August 8, 2023 At 8:21 am
  
  Hi Jorge Soria,
  
  VLOOKUP or XLOOKUP cannot retrieve multiple rows of matching records for the same search key.
  The function you can rely on is QUERY or FILTER with the REDUCE LHF.
  
  Please give this formula a try.
  
  =REDUCE({"DATOS","DATOS","DATOS","DATOS","DATOS"},TOCOL(B2:B,1), LAMBDA(accu,key,IFNA(VSTACK(accu,HSTACK(key,FILTER( CHOOSECOLS('P. ALBA 2022'!C:Z,{8, 3, 9, 11, 10}), 'P. ALBA 2022'!C:C=key))))))
  
  I hope this is helpful!
  
Shi Yu April 23, 2023 At 5:21 am

I appreciate this solution!

My data is spread across multiple columns that are non-repeating.

E.g.:

In the example below, 1-4 represents the list of colleagues in a team, and the fifth column represents the team leader.

If I would like to find out all the team leads that “JACK” has, what formula should I use?

A1 | A2 | A3 | A4 | team lead
JACK | albert | sally | roman | adam
jonas | rocky | gene | chris | alex
John | JACK | William | Robert | eve

- Prashanth April 25, 2023 At 10:21 am
  
  Hi, Shi Yu,
  
  There are several ways to achieve this. One way to do this is using the below formula in cell F2.
  
  I assume the range is A1:E where A1:E1 contains the headers.
  
  =textjoin(", ",1,unique(tocol(bycol(A2:E,lambda(c,ifna(filter(E2:E,c="Jack")))))))
  
andrei December 26, 2022 At 5:36 am

Is it possible to make an out-of-range occurrence return nothing?

In your example, the 4th occurrence of Litchi would return values for the last mango.

- Prashanth December 27, 2022 At 2:33 pm
  
  Hi, Andrei,
  
  Sorry, I could not replicate that issue within my Sheet. I suggest you share your example Sheet.
  
Nick Johns December 19, 2022 At 7:01 am

Hello, this worked great for me! Thank you. But is there any way to add a second criteria column?

I want to look up all invoices that match the customer ID and then also check those against the “Status” column to see that it is paid.

I tried combining the criteria in this formula, but it didn’t work.

=IFERROR(INDEX(Invoices!C$2:C,LARGE(IF(and((isnumber(find("Paid", Invoices!E$2:E))=FALSE),Invoices!B$2:B=C3), ROW(Invoices!$B$2:$B)-1),1)))

Any help is MUCH appreciated. Again, thanks for the helpful article!!!

- Prashanth December 19, 2022 At 12:09 pm
  
  Hi, Nick Johns,
  
  I assume Invoice!B2:B contains invoice numbers, and Invoice!E2:E is the status column.
  
  To filter Invoice!C2:C if Invoice!B2:B=C3 and Invoice!E2:E contains “Paid”, use the following filter.
  
  =filter(Invoices!C2:C,Invoices!B2:B=C3,regexmatch(Invoices!E2:E,"Paid"))
  
  It will return a multi-row output. You can use Index to offset rows and get the nth value.
  
  E.g.:
  
  =index(filter(Invoices!C2:C,Invoices!B2:B=C3,regexmatch(Invoices!E2:E,"Paid")),3)
  
Dale Harper August 22, 2022 At 11:57 pm

Hi, This has been an absolute lifesaver! Does this work with Hlookup?

- Prashanth August 23, 2022 At 9:37 am
  
  Hi, Dale Harper,
  
  Yep! But you may require to slightly adjust the formula.
  
  I’ll update you soon!
  
Irving Beltran April 18, 2021 At 3:52 am

Hi,

I hope you’re doing well. I used your formula, and it works great for finding the first two occurrences of an item. But it just won’t work for the 3rd, 4th, and 5th occurrences. This is my modified formula:

=INDEX(filter(A2:AB,((B2:B=AK2))*((AB2:AB="No recibido"))),3,28)

I get an error message that says, “The second parameter is 3. Valid values must be between 0 and 2.” Could you please help me find a way to bypass this parameter restriction?

- Prashanth April 19, 2021 At 8:41 am
  
  Hi, Irving Beltran,
  
  You will see that error when there is no third occurrence.
  
  You might want to use the OR condition in FILTER.
  
  =index(filter(A2:AB,((B2:B=AK2)+(AB2:AB="No recibido"))),3,28)
  
  This may help – How to Use AND, OR with Google Sheets Filter Function – Advanced Use.
  
  - Irving Beltran April 19, 2021 At 8:00 pm
    
    Hi,
    
    Thanks for your quick response. I was able to fix my formula by clicking on the link you sent me and by learning a bit more from other sections on your site. Keep up the awesome work! Godspeed!
    
David Austin January 9, 2021 At 11:10 pm

Hi, I’m a bit of a beginner but the formula worked really well. I wanted all the nth occurrences in my spreadsheet added together so I just did the formula with a plus sign and then the formula again with the next occurrence and repeated over and over again like this:

=ARRAYFORMULA(IFERROR(INDEX($Q$3:$Q,SMALL(IF($S$3=$P$3:$P,ROW($P$2:$P)-1),1))))+(IFERROR(INDEX($Q$3:$Q,SMALL(IF($S$3=$P$3:$P,ROW($P$2:$P)-1),2))))

This worked but I guess it’s not the proper way to do it. What is?

Thanks again

- Prashanth January 12, 2021 At 8:03 am
  
  Hi, David Austin,
  
  I think I can solve this. Can you please leave the URL of a sample sheet in your reply?
  
Sandra August 10, 2020 At 11:56 pm

I want to be able to do this on a separate sheet. That is, I have collected form responses and I created a new sheet. I want to lookup responses and have the formulate populate the answers to Vlookup in the new sheet. What would the new formula look like?

- Prashanth August 11, 2020 At 12:33 pm
  
  Hi, Sandra,
  
  You have not given the data range or enough information. When you say a new sheet, were you referring to a new file or a new tab in the same form response sheet?
  
Will March 17, 2020 At 10:20 pm

Nice formula!

If I want to use it without the INDEX, and just have it return the ROW numbers, can it be made into an Array Formula?

Thank you

- Will March 17, 2020 At 11:03 pm
  
  In fact, I wouldn’t need SMALL either as I want all the row numbers that meet the criteria. This formula produces an array ARRAYFORMULA(IF(value=range,ROW(A1:A))) I get a long list of FALSE with the occasional ROW number interspersed. My question now is – how do I show a list with just the ROW numbers?
  
  Thank you
  
  - Will March 17, 2020 At 11:44 pm
    
    FILTER and ISNUMBER seem to work!
    
    - Prashanth March 18, 2020 At 11:19 am
      
      Hi, Will,
      
      No need to use the IF test. You can directly use Filte or Query.
      
      Can you show me a demo of your problem by sharing an example sheet? I may be able to help you.
      
Peter Kruger November 20, 2019 At 9:47 am

Is this still working? When I applied it to my sheet, it gave me a formula parse error so I fully recreated your sheet exactly and copy and pasted your formula and got the same error. Any advice?

- Prashanth November 21, 2019 At 4:55 pm
  
  Hi Peter,
  
  Once copy-paste, re-type the double quotation marks. It causes issues in copy-paste.
  
  Best,
  
AJ September 20, 2018 At 11:19 pm

I really appreciate this, it was very helpful and clearly laid out. One question: is the “ArrayFormula” necessary? I tried it without “ArrayFormula” and it still seems to work. I am fairly new to using “ArrayFormula” and am looking to learn more. I figured I would inquire what purpose it serves in this formula.

- Prashanth September 21, 2018 At 8:17 am
  
  Hi, AJ,
  
  I am glad to hear that you liked this tutorial on Nth occurrence!
  
  Now regarding your question, use the ArrayFormula function with non-array functions.
  
  For example, in our formula that finds Nth occurrence in Google Sheets, I have used the function ROW in a range (array). The ROW is a non-array function. So I’ve used the ArrayFormula.
  
  But there are certain Exceptions. If you use a non-array function, here example the ROW function, within an array formula like FILTER or INDEX, there is no need to again use the ArrayFormula. Here Index itself is an array formula.
  
  So ArrayFormula is not a must here.
  
  I have used the ArrayFormula to avoid the confusion.
  
  Thanks.
  
Ken Snyder September 1, 2018 At 6:28 am

Very good stuff. I really like using named-ranges for things and instead of the A2:A,ROW(A2:A)-1 in the example I’m wondering how I could get this to work. I have a named range called PEOPLE_NAMES which is a column of names.

- Prashanth September 1, 2018 At 8:57 am
  
  Hi,
  
  You can use named ranges. But not advisable as you may require to use Query to extract individual columns from the multiple columns named range.
  
Tim Watson August 31, 2018 At 2:35 am

This is great! Only trouble for me is that the arrayformula isn’t populating down the page (just showing result in the cell where the formula is). Any advice?

- Prashanth August 31, 2018 At 7:00 am
  
  Hi, Tim,
  
  I am glad that you liked it.
  
  Regarding expanding result, I think it’s not possible because of the use of INDEX which doesn’t support.
  
RSigu January 31, 2018 At 7:11 pm

It Worked like a charm!!! Thank you!!! 🙂