ASSIGNMENT – 24
IF & VLOOKUP — Answer Key
Table 1 – Compare Two Lists
Formula in C2
=IF(A2=B2,"Matching","Not Matching")Drag the formula from C2 to C11.
| List 1 | List 2 | Result |
|---|---|---|
| Amit | Rahul | Not Matching |
| Neha | Neha | Matching |
| Rahul | Amit | Not Matching |
| Pooja | Pooja | Matching |
| Karan | Mohit | Not Matching |
| Simran | Simran | Matching |
| Mohit | Karan | Not Matching |
| Riya | Riya | Matching |
| Arjun | Varun | Not Matching |
| Varun | Arjun | Not Matching |
Q.2 Neha
Answer: Matching
Q.3 Pooja
Answer: Matching
Q.4 Karan
Answer: Not Matching
Q.5 Count Matching Records
=COUNTIF(C2:C11,"Matching")Answer: 4 Matching records
Matching records:
-
Neha
-
Pooja
-
Simran
-
Riya
Table 2 – Check Whether Value Exists in List 1
Formula
If List 1 is in A2:A11 and List 2 is in B2:B11, enter in C2:
=IFERROR(VLOOKUP(B2,$A$2:$A$11,1,FALSE),"Value Not Found")Then drag down.
| List 1 | List 2 | Result |
|---|---|---|
| 45821 | 78214 | Value Not Found |
| 63542 | 63542 | 63542 |
| 91436 | 52781 | 52781 |
| 52781 | 91436 | 91436 |
| 74625 | 31859 | 31859 |
| 31859 | 74625 | 74625 |
| 85214 | 85214 | 85214 |
| 29647 | 74152 | 74152 |
| 74152 | 29647 | 29647 |
| 56328 | 99999 | Value Not Found |
Q.7 Does 63542 exist in List 1?
Answer: 63542 — Found
Q.8 Does 99999 exist in List 1?
Answer: Value Not Found
Q.9 Does 74152 exist in List 1?
Answer: 74152 — Found
Table 3 – Product Category Lookup
Product Master
| PRODUCT | CATEGORY |
|---|---|
| Laptop | Electronics |
| Keyboard | Computer Accessories |
| Mouse | Computer Accessories |
| Printer | Electronics |
| Scanner | Electronics |
| Monitor | Electronics |
| Webcam | Computer Accessories |
| Speaker | Electronics |
VLOOKUP Formula
If the search product is in D2:
=VLOOKUP(D2,$A$2:$B$9,2,FALSE)Drag the formula down.
Q.10 Answers
| Product | Category |
|---|---|
| Laptop | Electronics |
| Mouse | Computer Accessories |
| Printer | Electronics |
| Webcam | Computer Accessories |
| Speaker | Electronics |
| Scanner | Electronics |
Final Answer Summary
| Q. | Answer |
|---|---|
| Q1 | Matching / Not Matching using IF |
| Q2 | Neha – Matching |
| Q3 | Pooja – Matching |
| Q4 | Karan – Not Matching |
| Q5 | 4 Matching records |
| Q6 | VLOOKUP + IFERROR |
| Q7 | 63542 – Found |
| Q8 | 99999 – Value Not Found |
| Q9 | 74152 – Found |
| Q10 | Laptop – Electronics; Mouse – Computer Accessories; Printer – Electronics; Webcam – Computer Accessories; Speaker – Electronics; Scanner – Electronics |
⭐ Important Point
For VLOOKUP, always remember:
Lookup value must be in the first column of the selected table.
And use:
FALSEfor an Exact Match.