10 Methods for Lookup with Multiple Criteria

This guide presents ten powerful functions and formulas for conducting lookups with multiple criteria in Excel. Imagine you need to find data for John in March; while this may seem straightforward, there are multiple ways to achieve it! From VLOOKUP to SUMPRODUCT and even the simplest SUM function, here are ten methods to help you.

1. VLOOKUP

Formula:

Detailed Explanation:

• E2 & F2: This concatenates the values in cells E2 and F2 (e.g., “John” and “March” into “JohnMarch”). This creates a unique lookup key.
• IF({1,0}, A2:A13 & B2:B13, C2:C13):
  • The expression A2:A13 & B2:B13 combines the values of columns A and B into a single array for lookup (e.g., “JohnMarch”).
  • The IF({1,0}, …) part is a trick used to create an array that allows the VLOOKUP function to work with multiple columns.
• 2: This specifies that VLOOKUP should return the value from the second column of the lookup array.
• 0: This indicates that the function should find an exact match.

2. LOOKUP

Formula:

Detailed Explanation:

• (A2:A13 = E2): This creates an array of TRUE/FALSE values where the name in column A matches E2.
• (B2:B13 = F2): This similarly creates an array for the month.
• * (Multiplication): When you multiply these two arrays, TRUE becomes 1 and FALSE becomes 0. This means only rows where both conditions are TRUE will yield 1.
• 0 / (…): Dividing by 0 will create an array that has 1s for rows that meet both conditions and errors for rows that do not.
• LOOKUP(1, …): This searches for the first occurrence of 1 in the array, effectively finding the first row where both conditions are satisfied and returns the corresponding value from C2:C13.

3. INDEX + MATCH

Formula:

Detailed Explanation:

• E2 & F2: This concatenates the lookup criteria into a single string.
• A2:A13 & B2:B13: This creates an array of concatenated values from columns A and B, similar to above.
• MATCH(…, …, 0): This looks for the first occurrence of the concatenated lookup key in the concatenated array of names and months. The 0 indicates an exact match is required.
• INDEX(C2:C13, …): After finding the position of the match, this function retrieves the corresponding value from column C.

4. OFFSET + MATCH

Formula:

Detailed Explanation:

• MATCH(E2 & F2, A2:A13 & B2:B13, 0): Similar to the previous methods, this finds the position of the concatenated lookup key.
• OFFSET(C1, …, 0): This starts from cell C1 and moves down by the number of rows returned by the MATCH function. The 0 indicates that there is no horizontal offset.

5. INDIRECT + MATCH

Formula:

Detailed Explanation:

• MATCH(E2 & F2, A1:A13 & B1:B13, 0): Finds the position of the concatenated lookup key.
• “C” & …: This constructs a cell reference string (e.g., “C5”).
• INDIRECT(…): This converts the string “C5” into a cell reference, allowing the formula to return the value from that cell.

6. SUM

Formula:

Detailed Explanation:

• (A2:A13 = E2): This creates an array of 1s (for TRUE) and 0s (for FALSE) where names in column A match E2.
• (B2:B13 = F2): This does the same for the month.
• * C2:C13: This multiplies the arrays together. Only values in C2:C13 where both conditions are TRUE will contribute to the sum.
• SUM(…): This adds up the results.

7. SUMIFS

Formula:

Detailed Explanation:

• C2:C13: This is the range to sum.
• A2:A13, E2: This specifies the first criteria range and the criteria. It sums values in C2:C13 where names match.
• B2:B13, F2: This specifies the second criteria range and criteria. It further filters the sums based on the month.

8. SUMPRODUCT

Formula:

Detailed Explanation:

• (A2:A13 = E2): Creates an array of 1s and 0s based on whether the name matches.
• (B2:B13 = F2): Creates a similar array for the month.
• * C2:C13: This multiplies the two condition arrays by the values in C2:C13. Only the corresponding values where both conditions are TRUE contribute to the sum.
• SUMPRODUCT(…): This sums the results of the multiplication.

9. DSUM

Formula:

Detailed Explanation:

• A1:C13: This is the database range that contains your data.
• 3: This specifies that the function should sum values from the third column (in this case, column C).
• E1:F2: This is the criteria range where the conditions are defined. It typically includes headers corresponding to the database columns.

10. MAX

Formula:

Detailed Explanation:

• (A2:A13 = E2): Generates an array of 1s and 0s where the names match.
• (B2:B13 = F2): Generates a similar array for the month.
• * C2:C13: This multiplies the condition arrays by the values in C2:C13. Only the corresponding values where both conditions are TRUE contribute to the result.
• MAX(…): This finds the largest value from the resulting array.

Conclusion

In Excel, performing lookups with multiple criteria can significantly enhance your data analysis capabilities. The ten methods outlined above offer various approaches to efficiently retrieve, sum, or analyze data based on more than one condition.

• VLOOKUP and LOOKUP are traditional functions that provide straightforward ways to find values, but they may have limitations with complex criteria.
• INDEX + MATCH and OFFSET + MATCH provide more flexibility, allowing you to work with arrays and perform lookups without being constrained to the first column.
• SUM, SUMIFS, and SUMPRODUCT enable you to aggregate data based on multiple conditions, which is especially useful for financial or performance data analysis.
• DSUM is ideal for database-style operations where criteria ranges are clearly defined.
• Finally, using functions like MAX can help you find the highest value under specific conditions, which is often crucial in performance evaluation scenarios.

By mastering these techniques, you can streamline your workflows, reduce errors, and create more dynamic spreadsheets that can adapt to varying data requirements. Whether you are dealing with simple data sets or complex databases, these formulas will empower you to extract meaningful insights efficiently.

Feel free to experiment with these methods in your own spreadsheets to gain a better understanding of how they work and to see which fits your specific needs best!

A0006