7 Ways to Lookup Multiple Criteria or Conditions in Excel

Deepanshu Bhalla 4 Comments

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.

Please click on the link below to download the Excel file used for explaining this article.

1. SUMPRODUCT
SUMPRODUCT : Multiple Criteria lookup value
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
SUMIFS : Multiple Criteria in Excel
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)
SUM Array Formula : Multiple Criteria lookup value

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
LOOKUP Formula
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
INDEX-MATCH : Multiple Criteria lookup value

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)
INDEX-MATCH Version 2: Multiple Criteria lookup value
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
Multiple Criteria Lookup

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
Spread the Word!
Share
About Author:
Deepanshu Bhalla

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.

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

    ReplyDelete
  2. PREMLATA2733@GMAIL.COM
    PROVIDE THE EXCEL SHEET

    ReplyDelete
  3. 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

    ReplyDelete
Next → ← Prev