To find the last occurrence of multiple criteria, no doubt, the function that we can use in Google Sheets is Lookup.

You may have come across similar tutorials on this blog. Here are such two tutorials.

1. How to Find the Last Matching Value in Google Sheets.
2. Lookup Last Partial Occurrence in a List in Google Sheets.

In the above first tutorial, that about how to use Lookup to find the last occurrence of value, one of my readers have asked me about the possibility of using multiple criteria.

It’s something like this. He wants to Lookup one value in a column and returns a value from another column if a criteria match in a third column.

I know I am poor at explaining the same. So please see the below picture (illustration).

The above dataset is unsorted. So first we are going to use multiple criteria in Lookup in an unsorted data set.

Lookup to Find the Last Occurrence of Multiple Criteria and Return a Value (Un-Sorted List)

In the above example screenshot, the criteria are 236 (Price) and “York” (Store Location).

If you only consider 236 (Price) as the criterion, the last occurrence would be in cell B9. But when you consider two criteria, then the matching value would be in cell B5 (B5:C5).

Google Sheets Formula to Find the Last Occurrence of Multiple Criteria in a Dataset:

=ArrayFormula(LOOKUP(2,1/(B2:B11&C2:C11="236York"),A2:A11))

Formula Explanation

At this juncture, I think I must drag your attention to the Lookup function syntax which is as follows.

LOOKUP(search_key, search_range, result_range)

In my formula, the search_key is 2! Yes, not 236 or “York”. This is because our search_range is now something like this.

To make you grasp this, I have entered the search_range formula in cell E2.

In this formula, ignore 1/ which is at the beginning of the formula. Leaving that, wherever the criteria match, the formula actually returns TRUE or FALSE.

TRUE and FALSE are Boolean values which are equal to 1 and 0 respectively. When these values (TRUE/FALSE) are divided by one, the result would be as in column E.

That means;

=1/TRUE

Result: 1

=1/FALSE

Result: DIV/0!

In Lookup, if the given search_key is not found (here 2), the search_key used in the lookup will be the value that’s immediately smaller in the range provided.

Further, when there are multiple occurrences, then the last occurrence will be considered. That’s why I have used the criterion 2.

This search range (virtual) helps Lookup to find the last occurrence of the multiple criteria.

You can use the function Lookup as above to find the last occurrence of multiple criteria in Google Sheets.

Lookup to Find the Last Occurrence of Multiple Criteria and Return a Value (Sorted List)

You may now have got some clue about how to use Lookup to find the last occurrence of multiple criteria in a sorted range and return a value. It may be as follows, right?

=ArrayFormula(lookup("236York",B2:B11&C2:C11,A2:A11))

This formula will definitely work if your dataset and criteria meet the below conditions.

1. The data must be sorted properly. How?
   • You must sort the data by column B then by column C since the criteria are from these two columns.
2. Both the criterion must be strings. But it is not true in our case. If the criteria are string and numbers, then you may sometimes see unexpected (wrong) results due to the combining of the search keys.

You can use my first formula in a sorted as well as an unsorted range. So use that and try to learn that thoroughly.

Conclusion

Actually, in the above multiple criteria use in Google Sheets Lookup function, are we really using multiple criteria? My answer is “NO”. We are using a single criterion in the Lookup.

I hope you have enjoyed this advanced Lookup tutorial.