7 Ways to lookup a value based on multiple criteria or conditions in Excel

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

Sample File

Download example file - Lookup based on multiple criteria

Solution

1. SUMPRODUCT



FORMULA :

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


2. SUM (ARRAY FORMULA)



FORMULA :

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

Hit CTRL+ SHIFT + ENTER to confirm this 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))}


3. SUMIFS


.

FORMULA :

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

This formula doesn't work in Excel 2003 or below versions.


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

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

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


5. 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. 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 :

{=INDEX(D3:D10,MATCH(1,IF(B3:B10=D12,IF(C3:C10=D13,1)),0))}


6. LOOKUP



FORMULA :

=LOOKUP(2,1/(B3:B10=D12)/(C3:C10=D13),(D3:D10))

7. IF NO MATCH FOUND



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.
Hit CTRL+ SHIFT + ENTER  to confirm this 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 :

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

Download the workbook

Excel Tutorials : 100 Excel Tutorials

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*

Related Posts:

0 Response to "7 Ways to lookup a value based on multiple criteria or conditions in Excel"

Post a Comment

Next → ← Prev