Lookup Last Partial Occurrence in a List in Google Sheets

Published on

To find the last instance of a value in Google Sheets, the function LOOKUP will come in handy for you. Can we also use this function to Lookup the last partial occurrence of a value in a list in Google Sheets?

Not possible in the usual way but there is a simple workaround that I am going to explain here.

Please note that this tutorial is about the partial matching of the last instance (occurrence) of a value. You can find slightly different topics on this site (links under “Conclusion” at the bottom).

I am using a combination of the functions LOOKUP and FIND to lookup the last partial occurrence of a value in a list in Google Sheets.

You can use my formula in the following scenarios or better to say formula that fulfills your following queries.

  • To Lookup a last partial occurrence in a sorted list.
  • To Lookup a last partial occurrence in an unsorted list.
  • Lookup last occurrence in a sorted as well as an unsorted list (this you can do in a different way also). See the second topic under the “Similar Lookup Tutorials” above.

How to Lookup Last Partial Occurrence in a List in Google Docs Sheets

Scenario

I had maintained a list of employees who were absent on weekly meetings in Google Sheets. If multiple employees were absent, their names entered in the sheet as comma-separated (this makes the partial matching relevant).

In a second column, against each name (or names as comma-separated), I had dates entered for them to report the manager who chaired the meeting.

Expected Result

What I want is to Lookup the last instance of an employee’s name and return the value from a different column. I mean when was an employee last absent in a meeting and his reporting date to his boss.

Again I am repeating since the names are comma-separated I may want to find partial occurrence.

Take a look at the sample data below to understand this. Also, find the Lookup formula in cell D2.

Sample Data

Formula to Lookup Last Partial Occurrence in Google Sheets

Formula to Lookup Last Partial Occurrence in a List (the Array A2:B8) in Google Sheets

=ArrayFormula(lookup(1,find("Samuel Barnes",A2:A)/find("Samuel Barnes",A2:A),B2:B))

I am sure you are going to love this formula hack because this formula works equally well in sorted as well as in an unsorted list.

Lookup + Find in Partial MatchFormula Explanation

As you may know, we can use the FIND function in Google Sheets to find the position of a string in a cell. If the string to find is not available in the search, then the function would return the ‘#VALUE!’ error.

We can use the ArrayFormula function with FIND to expand the search into a range (array). See the example below to understand how it works.

Syntax: SEARCH(search_for, text_to_search, [starting_at])

See the FIND in an array form in cell D2. The formula in cell D2 searches for (search_for) the string “Samuel Barnes” in the range (text_to_search) A2:A8.

=ArrayFormula(find("Samuel Barnes",A2:A8))
The use of FIND formula in a range in Google Sheets

As you can see, wherever the formula finds a match it returns the position of the string else the #VALUE! error.

Let’s divide this formula output with the same formula output.

=ArrayFormula(find("Samuel Barnes",A2:A8)/find("Samuel Barnes",A2:A8))

This will leave a column of data with the values 1 and #VALUE! errors as below.

Partial Match using Find in Lookup

This is an important step to Lookup the last partial occurrence of a value in a list in Google Docs Sheets. Because this FIND array output is our virtual Lookup range.

Syntax: LOOKUP(search_key, search_range|search_result_array, [result_range])

When you scroll up and check my formula that Lookup the last partial occurrence, you can see that I have used 1 as the Lookup search key instead of “Samuel Barnes”. Because there is no name with this person in the virtual Lookup range returned by the FIND array formula.

The Lookup is known for returning the last value match. Since the Lookup key is 1, the formula would return the value from the cell in column B against the last value 1 in column A.

This is the easiest way to Lookup the last partial occurrence in a list in Google Sheets.

I am repeating, you can use this formula in a sorted dataset, unsorted dataset, and even partial and full match. No need to make any changes in it by virtue of the virtual range.

Partial Case In-sensitive Lookup in Sheets

The above formula is case sensitive. It treats capital and small case letters as differently. For example, the formula would consider “japan” and “Japan” as two different strings.

How can I make the partial lookup formula case insensitive?

It’s very simple! Just replace the function FIND in the formula with the SEARCH function.

=ArrayFormula(lookup(1,search("Samuel Barnes",A2:A)/SEARCH("Samuel Barnes",A2:A),B2:B))

If you find this Lookup + Find or Lookup + Search combination useful, feel free to share the post.

Conclusion

Please don’t get confused with the following similar topics on this site. They are slightly different.

Similar Lookup Tutorials:

  1. Partial Match in Vlookup in Google Sheets.
  2. Find the Last Matching Value in Google Sheets in Sorted/Unsorted Data.
  3. Vlookup to Find Nth Occurrence in Google Sheets.

Thanks for the stay. Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

2 COMMENTS

  1. How would I do multiple find() in the lookup()?

    For example, let’s say column C is a list of reasons they were absent (sick, vacation, etc). I want to find the last occurrence of “Samuel Barnes” in column A and “sick” in column C and return the date from column B.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.