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))}
.
FORMULA :
=SUMIFS(D3:D10,B3:B10,D12,C3:C10,D13)
This formula doesn't work in Excel 2003 or below versions.
4. INDEX-MATCH
=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
WHAT IF STUDENT BELONG FROM SAME SECTION AND SAME FIRST NAME.?
ReplyDeletePREMLATA2733@GMAIL.COM
ReplyDeletePROVIDE THE EXCEL SHEET
Thank you! What a big help!
ReplyDelete