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.

{=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**

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