Sometimes, we find ourselves needing to locate the Nth occurrence of a matching value in a table in Google Sheets. Is this even possible? Absolutely! And today, I’m going to share with you a simple yet powerful method to achieve this using Google Sheets’ built-in functions.
The Limitations of Traditional Formulas
Before we dive into the solution, let’s talk about the limitations of traditional formulas like INDEX and FILTER. While INDEX and FILTER can help us find the Nth occurrence of a value, they lack some of the advanced features of XLOOKUP, such as wildcard usage and the ability to search from the top or bottom.
Introducing XLOOKUP for Nth Occurrence Matching
To overcome these limitations, we’ll harness the power of XLOOKUP. What sets XLOOKUP apart is its versatility and ability to handle complex search requirements. In this article, we’ll concentrate on the first set of formulas, but don’t worry – the other sets are similar and easy to follow.
1. XLOOKUP Nth Match and Search from First to Last
Let’s start by looking at an example. In our sample dataset, we have country names in column A and corresponding figures in column B. Our goal is to find the Nth occurrence of a specific country and retrieve the corresponding value from column B.
Here’s a regular XLOOKUP formula that returns the 1st occurrence of the country name “USA”:
=ArrayFormula(XLOOKUP(E3:E4,B2:B,C2:C,"",0,1))To find the 2nd or 3rd occurrence, we modify this formula by using a running count. For example, to find the 2nd occurrence:
=ArrayFormula(XLOOKUP(E3:E4, FILTER(B2:B, COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))=2), FILTER(C2:C, COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))=2), "", 0, 1))And to find the 3rd occurrence, we make a similar modification:
=ArrayFormula(XLOOKUP(E3:E4, FILTER(B2:B, COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))=3), FILTER(C2:C, COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))=3), "", 0, 1))By comparing the 2nd and 3rd occurrence formulas, you’ll notice that the only difference is the highlighted part. Simply modify this part to find the Nth occurrence of a value from the first entry to the last entry.
Anatomy of the XLOOKUP Nth Match Formula
Let’s break down the formula step by step using the 2nd occurrence as an example:
- Search_key: E3:E4
- Lookup_range: Use the following running count formula to generate the running count of the occurrence of items in the XLOOKUP criteria field (column B):
=ArrayFormula(COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B)))In the 2nd occurrence formula, the Nth value is 2. Therefore, the lookup_range for XLOOKUP would be:
=FILTER(B2:B, COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))=2)- Result_range: Use the following formula to filter column C for the count of occurrences equal to 2:
=FILTER(C2:C, COUNTIFS(B2:B,B2:B,ROW(B2:B),"<="&ROW(B2:B))=2)- Missing_value: Leave this parameter blank.
- Match_mode: Set it to 0.
- Search_mode: Set it to 1.
And there you have it! You’ve successfully used XLOOKUP to find the Nth occurrence of a value from the first entry to the last entry in Google Sheets.
2. XLOOKUP Nth Match and Search from Last to First
If you need to find the Nth occurrence of a value from the last entry to the first entry, you can make a few changes to the formulas we’ve discussed earlier. In the regular formula (F3), replace 1 in the last part (match_mode) with -1. In the other formulas (G3 and H3), replace “<=” with “>=” in the running count formula to achieve a reverse running count.
3. Wildcards in XLOOKUP Nth Occurrence
Wildcards, such as the asterisk (*), are commonly used in formula searches. Using wildcards with XLOOKUP Nth occurrence match in Google Sheets is as simple as using them in regular formulas. For example, to find “US” instead of “USA,” modify the F3 formula as follows:
=ArrayFormula(XLOOKUP(E3:E4&"*", B2:B, C2:C, "", 2, 1))By appending an asterisk to the criteria part (E3:E4&”*”) and changing the match_mode from 0 to 2, you can match both “US” and “USA” in column B.
That’s all for now. I hope you found this article helpful and that it will enable you to leverage the power of XLOOKUP for finding the Nth occurrence of a value in Google Sheets. For more articles on Google Sheets and other valuable resources, visit Crawlan.com. Happy analyzing!
