Practical 11 – Mastering VLOOKUP

 1. Objective

The primary objective of this practical is to enable students to efficiently retrieve specific information from large datasets using Excel’s VLOOKUP function. Students will learn to:

·         Understand the syntax and arguments of the VLOOKUP function.

·         Perform exact matches to retrieve precise data.

·         Perform approximate matches for looking up values within ranges.

·         Handle common VLOOKUP errors, such as #N/A, using IFERROR or IFNA.

·         Utilize VLOOKUP to combine and analyze data from multiple tables.

·         Apply absolute references ($) effectively within VLOOKUP formulas.

2. Introduction

VLOOKUP stands for "Vertical Lookup" and is a powerful Excel function used to search for a value in the first column of a table and return a corresponding value from another column in the same row. This is indispensable when you need to pull specific pieces of information from a master list based on a common identifier. Imagine you have a student ID in one sheet and need to find their contact details, course enrollment, or even their grade from another sheet—VLOOKUP is the tool for this job. Mastering VLOOKUP will significantly improve your efficiency in data management and reporting.

3. Pre-requisites

·         Successful completion of Practical 3 and Practical 6 (understanding of cell references).

·         Basic understanding of Microsoft Excel interface.

·         Familiarity with basic formula entry.

4. Software Required

·         Microsoft Excel

5. Procedure: Practical Steps with Examples

We will start with a fresh workbook to demonstrate VLOOKUP, but you can imagine these tables existing on different sheets or even in the same sheet for demonstration purposes.

Step 5.1: Setting Up Data for VLOOKUP

1.     Open Excel: Launch Microsoft Excel and select Blank workbook.

2.     Rename Sheets:

o    Rename Sheet1 to Student Data.

o    Rename Sheet2 to Grades Lookup.

3.     Enter Student Data (on Student Data sheet):

o    In cell A1, type Student ID. In B1, type Student Name. In C1, type Course.

o    Populate with some sample data:

Student ID	Student Name	Course
S001	Alice Johnson	BCA
S002	Bob Williams	BCA
S003	Charlie Brown	BCA
S004	Diana Prince	BCA
S005	Eva Green	BCA

4.     Enter Grades Lookup (on Grades Lookup sheet):

o    In cell A1, type Student ID. In B1, type Final Grade. In C1, type Attendance %.

o    Populate with some sample data, ensuring some Student IDs from Student Data are present, and maybe one or two are missing or new:

Student ID	Final Grade	Attendance %
S001	A	95%
S003	B	88%
S005	C	75%
S006	A	92%
S002	B	80%

Step 5.2: Basic VLOOKUP for Exact Match

We want to add a Final Grade column to our Student Data sheet by looking up the Student ID in the Grades Lookup table.

1.     Go to Student Data sheet.

2.     Add Header: In cell D1, type Final Grade (from Lookup).

3.     Enter VLOOKUP Formula for Alice (S001): In cell D2, type the following formula:
=VLOOKUP(A2,'Grades Lookup'!A:C,2,FALSE)

o    A2: This is the lookup_value (the Student ID "S001" for Alice that we want to find).

o    'Grades Lookup'!A:C: This is the table_array (the range where we want to search). We are searching in columns A, B, and C of the Grades Lookup sheet.

§  Important: The lookup_value must be in the first column of your table_array (column A in this case).

o    2: This is the col_index_num (the column number in the table_array from which to retrieve the value). Final Grade is in the 2nd column of 'Grades Lookup'!A:C.

o    FALSE: This is the range_lookup argument. FALSE (or 0) specifies an exact match. Excel will only find "S001" and not "S001.5".

4.     Press Enter. Alice’s final grade ("A") should appear.

5.     Copy Down: Drag the fill handle from D2 down to D6 to apply the formula to all students.

o    Observation:

§  You should see grades for S001, S002, S003, S005.

§  For S004, you will see #N/A. This means Excel couldn’t find "S004" in the Grades Lookup table. This is a common VLOOKUP error.

Step 5.3: Handling #N/A Errors with IFERROR

The #N/A error can make a sheet look messy. We can use the IFERROR function (or IFNA which specifically targets #N/A) to display a more user-friendly message.

1.     Modify Formula for Alice: In cell D2 (on Student Data sheet), edit the formula:
=IFERROR(VLOOKUP(A2,'Grades Lookup'!A:C,2,FALSE),"Grade Not Found")

o    Explanation: If the VLOOKUP returns an error (like #N/A), IFERROR will display "Grade Not Found" instead.

2.     Copy Down: Drag the fill handle from D2 down to D6.

o    Observation: For Diana (S004), you should now see "Grade Not Found".

Step 5.4: Retrieving Another Column (Attendance %) and Absolute References

To make the table_array stable when copying formulas across columns, we should use absolute references.

1.     Go to Student Data sheet.

2.     Add Header: In cell E1, type Attendance % (from Lookup).

3.     Modify VLOOKUP in D2 with Absolute Reference for table_array:
=IFERROR(VLOOKUP(A2,'Grades Lookup'!$A:$C,2,FALSE),"Grade Not Found")

o    Explanation: The $ signs in $'Grades Lookup'!$A:$C ensure that when we copy this formula, the table_array always refers to columns A through C on the Grades Lookup sheet.

4.     Copy this Modified Formula from D2 to E2: Select D2, copy (Ctrl+C), select E2, paste (Ctrl+V).

o    Observation: You will see the Final Grade still, but the formula is now VLOOKUP(B2,.... We need to adjust lookup_value and col_index_num.

5.     Adjust Formula in E2:
=IFERROR(VLOOKUP(A2,'Grades Lookup'!$A:$C,3,FALSE),"Data Not Found")

o    Explanation:

§  We changed 2 to 3 for col_index_num because Attendance % is in the 3rd column of our Grades Lookup table.

§  We changed lookup_value from B2 back to A2 to look up by Student ID.

§  Changed error message to "Data Not Found".

6.     Copy Down: Drag the fill handle from E2 down to E6.

o    Observation: You should now see the Attendance % for students, with "Data Not Found" for those not in the lookup table.

Step 5.5: VLOOKUP for Approximate Match

An approximate match is useful when your lookup_value falls within a range. For example, to assign grades based on percentages.

1.     Go to a New Sheet: Rename Sheet3 to Grade Scale.

2.     Create a Grade Scale Table:

o    In cell A1, type Min Percentage. In B1, type Letter Grade.

o    Populate with the following:

Min Percentage	Letter Grade
0%	F
35%	Pass
46%	C
60%	B
80%	A

3.     Go back to Student Data sheet.

4.     Add Header: In cell F1, type New Grade.

5.     Enter VLOOKUP Formula: In cell F2, type the following formula:
=VLOOKUP(B2,'Grade Scale'!$A:$B,2,TRUE) (assuming your marksheet percentage is in B2. If it’s in H2 from Practical 3, use H2)

o    B2 (or H2 from marksheet): This is the lookup_value (the percentage for the student).

o    'Grade Scale'!$A:$B: This is the table_array (our grade scale table).

o    2: This is the col_index_num (Letter Grade is in the 2nd column).

o    TRUE: This specifies an approximate match. Excel finds the largest value in the first column that is less than or equal to the lookup_value.

6.     Copy Down: Drag the fill handle from F2 down to F6.

o    Observation: Grades should be assigned based on the percentages and the lookup table.

6. Exercises / Tasks

Apply the concepts learned to complete the following tasks:

1.     Student Contact Information:

o    On a new sheet named Contact Info, create a table with Student ID, Email, and Phone Number. Populate with 3-4 students from your Student Data sheet.

o    On your Student Data sheet, add new columns Email and Phone Number. Use VLOOKUP to retrieve this contact information for each student based on their Student ID. Handle #N/A errors gracefully (e.g., "N/A" or "No Contact").

2.     Course Details Lookup:

o    On a new sheet named Course Catalog, create a table with Course Name, Credits, and Department.

o    On your Student Data sheet, add new columns Credits and Department. Use VLOOKUP to retrieve this information based on the Course name for each student.

3.     Dynamic Grade Scale Update:

o    Modify your Grade Scale table. Add a new row for "Excellent" grade where Min Percentage is 90%.

o    Observe how the New Grade column in your Student Data sheet automatically updates without changing the VLOOKUP formula (as long as the table_array reference covers the new row).

4.     Price Lookup:

o    On a new sheet named Products, create a table with Product ID, Product Name, and Unit Price.

o    On a new sheet named Order, create a list of Product IDs and Quantity.

o    Use VLOOKUP on the Order sheet to automatically retrieve the Unit Price for each product. Then calculate the Total Price for each item (Quantity * Unit Price). Ensure proper error handling if a Product ID is not found.

7. Assessment / Deliverables

·         Submit the completed Excel workbook (.xlsx file) with the Student Data, Grades Lookup, Grade Scale sheets, and any new sheets created for the exercises.

·         Ensure all VLOOKUP formulas are correctly implemented for both exact and approximate matches.

·         Demonstrate proper error handling for #N/A results.

·         Verify that absolute references are used appropriately where necessary.

·         Be prepared to explain each argument of the VLOOKUP function and when to use exact vs. approximate match.