Excel Return Value Based on Two Criteria Easily

Retrieving values from a dataset based on multiple criteria is a common requirement in data analysis and Excel is a popular tool for this purpose. In this article, we'll explore how to return a value based on two criteria in Excel easily.

Using INDEX and MATCH Functions with Two Criteria

The INDEX and MATCH functions are powerful tools in Excel that can be used together to return a value based on two criteria. The INDEX function returns a value at a specified position in a range, while the MATCH function returns the relative position of a value within a range.

Suppose we have a dataset with the following structure:

To return the score of a person with a specific name and age, we can use the following formula:

INDEX and MATCH with Two Criteria Formula:

=INDEX(D:D, MATCH(1, (A2=A:A) * (B2=B:B), 0))

Where:

• D:D is the column containing the scores
• A2 is the cell containing the name criterion
• A:A is the column containing the names
• B2 is the cell containing the age criterion
• B:B is the column containing the ages

This formula uses an array formula to match the two criteria and return the corresponding score.

Using FILTER Function with Two Criteria (Excel 2019 and Later)

In Excel 2019 and later versions, the FILTER function can be used to return a value based on two criteria. The FILTER function returns a range of values that meet a specified condition.

FILTER with Two Criteria Formula:

=FILTER(D:D, (A:A=A2) * (B:B=B2))

Where:

• D:D is the column containing the scores
• A:A is the column containing the names
• A2 is the cell containing the name criterion
• B:B is the column containing the ages
• B2 is the cell containing the age criterion

This formula returns an array of scores that meet the two criteria.

Common Errors and Troubleshooting

When using the INDEX and MATCH functions or the FILTER function with two criteria, common errors include:

• #N/A error: This error occurs when the criteria are not found in the dataset.
• #REF! error: This error occurs when the column or row references are incorrect.

To troubleshoot these errors, check the following:

• Verify that the criteria are correct and exist in the dataset.
• Check the column and row references to ensure they are correct.
• Make sure to enter the formula as an array formula by pressing Ctrl+Shift+Enter.

Best Practices

When working with large datasets and multiple criteria, it's essential to follow best practices to ensure accuracy and efficiency:

• Use absolute references to column and row references.
• Use named ranges to simplify the formula.
• Test the formula with sample data before applying it to the entire dataset.