This article explains multiple ways to lookup a value based on multiple criteria or conditions in Excel.
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.
FORMULA :
=SUMPRODUCT((B3:B10=D12)*(C3:C10=D13)*(D3:D10))
- The formula compares each cell in the range B3:B10 with the value in cell D12 (Name). It creates an array of TRUE/FALSE values.
- Compare each cell in the range C3:C10 with the value in cell D13 (Section), creating another array of TRUE/FALSE values.
- Keep the values in the range D3:D10 as they are.
- Multiply the corresponding elements of all three arrays together, considering only the cells where both conditions (matching values in B3:B10 and C3:C10) are satisfied.
- Sum up all the products obtained in the previous step, resulting in the desired output i.e. score of a student.
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)
The formula calculates the sum of the values in the range D3:D10 that meet multiple criteria. It adds up the values from D3:D10 where the corresponding cells in B3:B10 match the value in D12 and the corresponding cells in C3:C10 match the value in D13.
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))}
The formula fetches 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.
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))}
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 logical conditions (B3:B10=D12) and (C3:C10=D13). It then returns the corresponding value from the range D3:D10. This formula essentially performs a reverse lookup to find the last value that matches the specified criteria.
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
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.
WHAT IF STUDENT BELONG FROM SAME SECTION AND SAME FIRST NAME.?
ReplyDeletePREMLATA2733@GMAIL.COM
ReplyDeletePROVIDE THE EXCEL SHEET
Thank you! What a big help!
ReplyDeleteThe 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