Vlookup to Find Nth Occurrence in Google Sheets [Dynamic Lookup]

0
380
Find Nth Occurrence in Google Sheets

If you are searching for a way to perform dynamic lookup in Google Sheets, you are on the right page. Here is my complete solution to find Nth Occurrence in Google Sheets, the unsolved puzzle! What more! You can use this formula on your own sheets without any issue. No scripts used to find the Nth occurrence. Only the Google Sheets built-in functions used.

How to Find Nth Occurrence in Google Sheets

You can find Nth occurrence of a value or search key in a vertical lookup using our below Google Sheets formula. With this tutorial, you can learn how to do a vertical lookup using a search key and its 1st, 2nd, 3rd or 4th occurrence or match.

Understand What is Nth Occurrence in a Vertical Lookup in Google Sheets

Let me explain you what is Nth Occurrence of a Search key in a Data set.

What is Nth Occurrence in a Vertical Lookup

I have marked the 1st and 2nd occurrence of an item, “Mango” in the above screenshot. Hope you got the idea now. Here “Mango” is the search key or lookup item.

To vertically lookup for a value and return another value from another cell in the same row, we normally use VLOOKUP function. VLOOKUP is the blue-eyed boy of all spreadsheet users, no matter whether they are using Google Sheets or Microsoft Excel.

Vertical Lookup means search down in a column for an item.

Can we use VLOOKUP formula to Vertically Lookup First, Second or Third Occurrence or Match?

In normal way it’s not possible. We can use VLOOKUP to return first occurrence. For example, the below Vlookup formula can return first occurrence of the item “Mango” and related value from the same row.

=vlookup(“Mango”,A2:C6,2)

There is a better function in Google Sheets to do Vertical Lookup. It’s a combination of Match and Index Functions. It brings much dynamism to vertical as well as horizontal lookup in Google Sheets.

I have already explained how this Index-Match Combo works as an alternative to Vlookup in Google Spreadsheets. But there I didn’t mention this advanced part of Vertical Lookup, i.e., find Nth Occurrence in Google Sheets.

To find Nth Occurrence in a Vertical Lookup, you need a new combination of Formulas. Here is that.

INDEX, SMALL, ROW, ARRAY AND IF – A Killer Combination to Find Nth Occurrence in Vertical Lookup

I am going to use the above combination of formulas for our purpose. I am directly going to the formula. If you want to learn all these formulas individually, please do check our ultimate Google Sheets Functions Guide.

Now to the example:

How to Find 1st, 2nd match in vlookup in Google Sheets

In the above screenshot, I’ve marked the first, second, third and fourth occurrence of an item “Mango”. Now see the below four formulas. The formulas will fetch values from the Second column, i.e. under the field label “Quantities”

=ArrayFormula(iferror(INDEX(B2:B,SMALL(IF(“Mango”=A2:A,ROW(A2:A)-1),1))))

=ArrayFormula(iferror(INDEX(B2:B,SMALL(IF(“Mango”=A2:A,ROW(A2:A)-1),2))))

=ArrayFormula(iferror(INDEX(B2:B,SMALL(IF(“Mango”=A2:A,ROW(A2:A)1),3))))

=ArrayFormula(iferror(INDEX(B2:B,SMALL(IF(“Mango”=A2:A,ROW(A2:A)1),4))))

The first formula will search down for item “Mango” in the range A2:A and return the value 400 in the same row, second column. See the number 1, in Red colour, at the end of the formula. It decides which occurrence to be checked. Here 1, means it’s the first occurrence of the item “Mango”.

The second, third and fourth formulas are the same. But the only difference is the occurrence number. So these functions may fetch values 500, 600 and 700 respectively from column 2.

How Can I Use this Dynamic Lookup Formula in My Spreadsheet to Find Nth Occurrence

1. You can copy and paste the above formula and modify it. When you copy and paste the formula from this page, please do retype all the double quotes.

2. Change “Mango” in the formula with any other lookup item based on your data.

3. Change the Nth Occurrence Number in the formula. You can see the Occurrence Number at the end of the formula in Red Colour.

4. You should change this “ROW(A2:A)1″ part of the formula based on your range. For example, If your range is “A3:A”, you should use this formula part as “ROW(A2:A)2″.

5. Change “B2:B”. I used this range to return values based on the match found in “A2:A”.

This way you can use this formula as your own. But if you want to learn, how I formed the above formula, you should go through the below two tutorials. Before that see once again the formula in use.

=ArrayFormula(iferror(INDEX(B2:B,SMALL(IF(“Mango”=A2:A,ROW(A2:A)-1),1))))

1. For the Pink coloured part of the formula, check this Index formula tutorial.

2. The blue coloured part, I’ve already detailed in another tutorial. It’s Google Sheets SMALL+ROW formula combo.

This way you can successfully find Nth Occurrence in Google Sheets. Hope you enjoyed this advanced Lookup tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here