Chapter 8: Using VLOOKUP and HLOOKUP in Excel

Introduction to VLOOKUP and HLOOKUP

Excel’s VLOOKUP and HLOOKUP functions are powerful tools for looking up data in a table or range. They allow you to search for a value in one column or row and return a value in the same row or column from a different column or row. In this chapter, we will explore both functions, their key points, and practical examples.

VLOOKUP Function

1. Purpose: Searches for a value in the first column of a table and returns a value in the same row from a specified column.
2. Syntax:
   • =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
   • lookup_value: The value you want to search for.
   • table_array: The range of cells that contains the data. The first column of this range is where the function searches for the lookup_value.
   • col_index_num: The column number in the table_array from which to retrieve the value.
   • [range_lookup]: Optional. Use 1 for an approximate match or 0 for an exact match.
3. Example:
   • Data Table:

3. Formula: To find the age of the person with ID 2:

excel

Copy code

=VLOOKUP(2, A1:C3, 3, 0)

This searches for the value 2 in the first column and returns the value from the 3rd column in the same row, which is 25.

HLOOKUP Function

1. Purpose: Searches for a value in the first row of a table and returns a value in the same column from a specified row.
2. Syntax:

excel

Copy code

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

2. lookup_value: The value you want to search for.
3. table_array: The range of cells that contains the data. The first row of this range is where the function searches for the lookup_value.
4. row_index_num: The row number in the table_array from which to retrieve the value.
5. [range_lookup]: Optional. Use 1 for an approximate match or 0 for an exact match.
6. Example:
   • Data Table:

3. Formula: To find the name associated with ID 2:

excel

Copy code

=HLOOKUP(2, A1:C3, 2, 0)

This searches for the value 2 in the first row and returns the value from the 2nd row in the same column, which is Bob.

Key Points

• Selecting the Table: Always ensure that the table_array starts with the column or row where your lookup_value is found. For VLOOKUP, this means the lookup value must be in the first column of the table. For HLOOKUP, the lookup value must be in the first row.
• Exact vs. Approximate Match: Use 0 for an exact match and 1 for an approximate match. If omitted, 1 is the default.
• Using CTRL + A: When entering these functions, use CTRL + A to open the expanded formula window. This helps you easily select and adjust your table_array and other parameters without manually entering commas and brackets.

Practice Assignment:

Create a Lookup Sheet

1. Set Up Your Data:
   • VLOOKUP Data Table: Create a table with employee IDs, names, and ages.
   • HLOOKUP Data Table: Create a table with IDs in the first row, names in the second row, and ages in the third row.
2. Using Formulas:
   • VLOOKUP:
     • To find the age of the employee with ID 4, use:
     • =VLOOKUP(4, A1:C4, 3, 0)
   • HLOOKUP:
     • To find the name associated with ID 3, use:
     • =HLOOKUP(3, A1:D4, 2, 0)
3. Experiment with Different Lookup Values:
   • Change the lookup values and table_array references to see how the formulas adapt.

Pro Tip:

When using VLOOKUP or HLOOKUP, make sure that your lookup value is unique within the table to avoid incorrect results. Additionally, consider using Named Ranges for your table_array to simplify your formulas and make them easier to understand and manage.

This chapter provides a comprehensive guide to using VLOOKUP and HLOOKUP functions in Excel. By practicing these functions, you’ll become proficient at retrieving specific data points from large tables, a crucial skill for efficient data analysis. At Anjni Computer Education, we aim to integrate practical applications into our lessons to help you master these essential Excel functions effectively.