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 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 Match – Formula 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))

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.

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!