ASSIGNMENT – 24

Use of Formulas – IF & VLOOKUP

Topic: Compare Two Lists, Check Values & Product Category Lookup

Table 1 – Compare Two Lists

Enter the following data in Excel:

LIST 1 LIST 2 RESULT
Amit Rahul  
Neha Neha  
Rahul Amit  
Pooja Pooja  
Karan Mohit  
Simran Simran  
Mohit Karan  
Riya Riya  
Arjun Varun  
Varun Arjun  

Questions – IF Function

Q.1 Compare List 1 and List 2. If both values are the same, display “Matching”, otherwise display “Not Matching”.

Q.2 Find whether Neha is Matching or Not Matching.

Q.3 Find whether Pooja is Matching or Not Matching.

Q.4 Find whether Karan is Matching or Not Matching.

Q.5 Count how many records are Matching.

Formula

In C2, enter:

=IF(A2=B2,"Matching","Not Matching")

Then drag the formula down.

Table 2 – Check Whether Value Exists in List 1

Enter the following data:

LIST 1 LIST 2 RESULT
45821 78214  
63542 63542  
91436 52781  
52781 91436  
74625 31859  
31859 74625  
85214 85214  
29647 74152  
74152 29647  
56328 99999  

Questions – VLOOKUP

Q.6 Using VLOOKUP, check whether the value from List 2 is available in List 1. If found, display the value; otherwise display “Value Not Found”.

Q.7 Check whether 63542 exists in List 1.

Q.8 Check whether 99999 exists in List 1.

Q.9 Check whether 74152 exists in List 1.

Formula

If List 2 starts from F2 and List 1 is in A2:A11, use:

=IFERROR(VLOOKUP(F2,$A$2:$A$11,1,FALSE),"Value Not Found")

Then drag the formula down.

Note: FALSE is used for an Exact Match.

Table 3 – Product Category Lookup

Enter the following Product Master data:

PRODUCT CATEGORY
Laptop Electronics
Keyboard Computer Accessories
Mouse Computer Accessories
Printer Electronics
Scanner Electronics
Monitor Electronics
Webcam Computer Accessories
Speaker Electronics

Search List

PRODUCT CATEGORY
Laptop  
Mouse  
Printer  
Webcam  
Speaker  
Scanner  

Q.10 Using VLOOKUP, find the Category of:

  1. Laptop

  2. Mouse

  3. Printer

  4. Webcam

  5. Speaker

  6. Scanner

Formula

If the Product Master table is in A2:B9 and the search product is in D2:

=VLOOKUP(D2,$A$2:$B$9,2,FALSE)

Then drag the formula down.

Formula Reference

1. IF – Compare Two Cells

=IF(A2=B2,"Matching","Not Matching")

2. VLOOKUP – Check Whether a Value Exists

=IFERROR(VLOOKUP(F2,$A$2:$A$11,1,FALSE),"Value Not Found")

3. VLOOKUP – Find Product Category

=VLOOKUP(D2,$A$2:$B$9,2,FALSE)

4. Count Matching Records

After applying the IF formula:

=COUNTIF(C2:C11,"Matching")

Skills Covered

By completing this assignment, students will learn:

  • IF Function

  • VLOOKUP

  • IFERROR

  • Exact Match

  • Comparing Two Lists

  • Checking Whether a Value Exists

  • Retrieving Related Information

  • COUNTIF for counting matching results

PreviousNext