Lookup to Find the Last Occurrence of Multiple Criteria in Google Sheets

Published on

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).

Lookup the Last Occurrence of Multiple Criteria

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.

Multiple Criteria Lookup Virtual Range

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.
Find the Last Occurrence of Multiple Criteria in sorted Range

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.

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.

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Compare Two Tables for Differences in Excel

To compare two tables with similar data for differences, you can use the XLOOKUP...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

More like this

Highlight Upcoming Birthdays in Google Sheets

When highlighting upcoming birthdays in Google Sheets, one important aspect is ignoring the year...

Calculate Weighted Average in Pivot Table in Google Sheets

You can calculate a weighted average in a Pivot Table using a custom formula...

Summarize Data and Keep the Last Record in Google Sheets

In Google Sheets, we can summarize data (like sum, average, min, max, count) using...

6 COMMENTS

  1. Superb, Thank you so much. Literally, I have been trying this for quite a long time. Thank you so much for always being very helpful. Looking forward to the explanation.

  2. Hello Prashanth,

    I am unable to apply the formula to get the last value against the two criteria.

    I am sharing a sheet with you so you could easily understand the issue.

    I am trying to apply the formula to get the last value from the closing stock by adding two criteria’s cells i.e “date” & ” Raw material name”.

    Since I got the desired result but unable to drag that formula to get more results according to the criteria.

    Kindly see if you could help me with that.

    Sample Sheet – removed by admin –

    • Hi, Zyshan,

      The scenario was not the same as per my tutorial. There are several columns involved. So I have written another formula for you.

      You can use this formula in cell E10, then copy-paste down.

      =ifna(lookup($D$1,filter({'RM INVENTORY'!$D$8:$D,filter('RM INVENTORY'!$EZ$8:$HU,'RM INVENTORY'!$EZ$8:$HU$8=$B10)},'RM INVENTORY'!$D$8:$D=$D$1)))

      I will explain this formula in another tutorial soon.

  3. I have two criteria:

    a) One column consists of 3 values like C, D, L in different cells in any order.
    b) Another column has increasing numeric values in some cells and text type values in some cells.

    Now I am looking for a formula to check when “D” appeared last with the highest numeric value? With reference to that result, a numeric value in the third column is to be added with the corresponding value of the fourth column.

    • Hi, Puneet,

      I am considering columns A, B, C, and D for the said data range. A1:D1 contains headers so the real range for Vlookup is A2:D.

      Assume the increasing numeric values (in B2:B) don’t repeat. If so you can try this Vlookup combo formula.

      =sum(ArrayFormula(vlookup("D",sort(filter(A2:D,isnumber(B2:B)),2,0),{3,4},0)))

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.