How can I find the first occurrence in Excel based on multiple criteria?

By /

Finding the first occurrence in Excel based on multiple criteria can be achieved by using the “INDEX” and “MATCH” functions. This method allows the user to specify specific criteria and return the first matching value from a specified range. By combining these functions with conditional statements, the user can effectively search for and retrieve the desired data based on multiple criteria, providing a more efficient and accurate way of locating specific information within a large dataset. This feature is particularly useful for data analysis and organizing large amounts of data in Excel.

Excel: Find First Occurrence Based on Multiple Criteria

You can use the following formula to find the first occurrence of a value in a column in Excel based on multiple criteria:

=INDEX(C2:C13,MATCH(1,INDEX((A2:A13=F1)*(B2:B13=F2),),FALSE))

This particular formula returns the first value in the range C2:C13 where the corresponding value in A2:A13 is equal to the value in cell F1and the corresponding value in B2:B13 is equal to the value in cell F2.

The following example shows how to use this formula in practice.

Example: Find First Occurrence Based on Multiple Criteria in Excel

Suppose we have the following dataset that contains information about points scored by various basketball players:

Suppose we would like to return the points value for the first occurrence of a player who is on the Spurs team and has a position of Forward.

We can specify this criteria in cells F1 and F2, then type the following formula into cell F3:

=INDEX(C2:C13,MATCH(1,INDEX((A2:A13=F1)*(B2:B13=F2),),FALSE))

The following screenshot shows how to use this formula in practice:

Excel find first occurrence based on multiple criteria

The formula returns a points value of 31, since this represents the points value for the first player to be on the Spurs team and have a position of Forward.

Note that if we change the criteria in cells F1 and F2, the formula will automatically return a new player who matches the new criteria.

For example, suppose we change the team to Rockets and the position to Guard:

The formula correctly returns a value of 12, which is the points value that corresponds to the first player to be on the Rockets team and have a position of Guard.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

x

Related terms:

  1. How to Find First Occurrence Based on Multiple Criteria in Excel?
  2. How to return multiple values based on single criteria in Excel?
  3. Excel: How to Count Unique Values Based on Multiple Criteria
  4. How can I count unique values in Google Sheets based on multiple criteria?
  5. How can I use Google Sheets to return multiple values based on a single criteria?
  6. How can Google Sheets return multiple values based on a single criteria?
  7. How can I use Excel to calculate the sum of data based on specific column and row criteria?
  8. Find Earliest Date Based on Criteria in Excel
  9. How to hide rows based on criteria in excel?
  10. How do I Pull Data from Another Sheet Based on Criteria in Excel?