# 7 Ways to Lookup Multiple Criteria or Conditions in Excel

This article explains multiple ways to lookup a value based on multiple criteria or conditions in Excel.

Scenario

Suppose you have data in Excel for students' first names with their sections and final scores. You are asked to find the score of a student given its name and section.

1. SUMPRODUCT
FORMULA :
=SUMPRODUCT((B3:B10=D12)*(C3:C10=D13)*(D3:D10))
• B3:D10 : Range in which you want to apply the first criteria (i.e. Name).
• C3:D10 : Range in which you want to apply the second criteria (i.e. Section).
• D3:D10 : Range from which you want to extract the desired value (i.e. section).
• D12 : Criteria 1 (i.e. Name).
• D13 : Criteria 2 (i.e. Section).

The formula checks if cells in B3:B10 match the criteria in D12 (Name) and C3:C10 match D13 (Section). It creates TRUE/FALSE arrays. These arrays are multiplied with D3:D10, summed up and returns the matching value from D3:D10 range (i.e. student's score).

2. SUMIFS
FORMULA :
=SUMIFS(D3:D10,B3:B10,D12,C3:C10,D13)

This SUMIFS formula returns the value from the range D3:D10 that meet multiple conditions i.e. (B3:B10 = D12) and (C3:C10 = D13).

3. SUM (ARRAY FORMULA)

FORMULA :
=SUM((B3:B10=D12)*(C3:C10=D13)*(D3:D10))

Hit CTRL+ SHIFT + ENTER to confirm this formula instead of the usual ENTER key as it is an array formula. If done correctly, Excel will automatically place curly braces {...} around the formula.

After placing curly braces, the formula would look like this in formula bar :

{=SUM((B3:B10=D12)*(C3:C10=D13)*(D3:D10))}
4. LOOKUP
FORMULA :
=LOOKUP(2,1/(B3:B10=D12)/(C3:C10=D13),(D3:D10))

The formula searches for the value "2" in the array created by dividing 1 by the conditions (B3:B10=D12) and (C3:C10=D13). It then returns the corresponding value from the range D3:D10. This formula performs a reverse lookup to find the last value that matches the specified criteria.

5. INDEX-MATCH

FORMULA :
=INDEX(D3:D10,MATCH(1,(B3:B10=D12)*(C3:C10=D13),0))

Hit CTRL+ SHIFT + ENTER to confirm this formula. If done correctly, Excel will automatically place curly braces {...} around the formula.

The formula pulls the value from the range D3:D10 that meets the criteria specified. It uses the MATCH function to find the position of the first occurrence of "1" in the array created by multiplying (B3:B10=D12) and (C3:C10=D13). The INDEX function then returns the corresponding value from the range D3:D10 at that position.

6. INDEX-MATCH (Version 2)
FORMULA :
=INDEX(D3:D10,MATCH(1,IF(B3:B10=D12,IF(C3:C10=D13,1)),0))

Hit CTRL+ SHIFT + ENTER to confirm this formula as it's an array formula.

7. Multiple Criteria Lookup: Handling No Match Cases

FORMULA :
=IFERROR(INDEX(D3:D10,MATCH(1,(B3:B10=D12)*(C3:C10=D13),0)),"No Match")

This formula returns "No Match" if no value exists on the basis of conditions. Press CTRL+ SHIFT + ENTER to confirm this array formula.

Related Posts
Share

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.

Post Comment 4 Responses to "7 Ways to Lookup Multiple Criteria or Conditions in Excel"
1. WHAT IF STUDENT BELONG FROM SAME SECTION AND SAME FIRST NAME.?

2. PREMLATA2733@GMAIL.COM
PROVIDE THE EXCEL SHEET

3. Thank you! What a big help!

4. The explanation of 4. INDEX-MATCH was exactly what I was looking for - works great when comparing 2 conditions in different worksheets and returning a third value

Next → ← Prev