The VLOOKUP Function in Excel: A Comprehensive Guide|2025

Discover how to use the VLOOKUP function in Excel to quickly find and retrieve data from tables. Master this essential tool for efficient data analysis and streamlined workflows!

Microsoft Excel is a powerful tool used for data management, analysis, and visualization. One of its most commonly used functions is VLOOKUP (Vertical Lookup), which helps users search for values within a specified range. This paper explores the VLOOKUP function in Excel, providing practical examples, explanations of its usage across multiple sheets, and comparisons with similar functions such as HLOOKUP and XLOOKUP.

Table of Contents

What is VLOOKUP in Excel?

The VLOOKUP function in Excel is used to search for a specific value in the first column of a given range and return a corresponding value from another column within the same range. The function is particularly useful when working with large datasets and requires retrieving information efficiently.

Syntax of VLOOKUP

The VLOOKUP function follows this syntax:

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value to search for in the first column of the table.
table_array: The range containing the data.
col_index_num: The column number (starting from 1) from which to return the value.
range_lookup (optional): Determines whether an exact match (FALSE) or an approximate match (TRUE) is required.

VLOOKUP Formula in Excel with Example

To understand the VLOOKUP formula in Excel with an example, consider the following dataset:

Employee ID	Name	Department	Salary
101	Alice	HR	50000
102	Bob	IT	60000
103	Carol	Finance	70000
104	Dave	Marketing	55000

To find the salary of employee ID 103, the VLOOKUP formula would be:

=VLOOKUP(103, A2:D5, 4, FALSE)

This formula searches for 103 in the first column of A2:D5, finds it in row 3, and returns the value from the 4th column, which is 70000.

How to do VLOOKUP in Excel with Two Spreadsheets

VLOOKUP can be used across different spreadsheets within the same workbook. Assume we have two sheets: Sheet1 contains Employee IDs, and Sheet2 contains employee salaries.

If we want to fetch the salary from Sheet2 into Sheet1, we use:

=VLOOKUP(A2, Sheet2!A2:B100, 2, FALSE)

This formula looks up the value in A2 in Sheet2 and returns the corresponding salary from column 2.

VLOOKUP from Another Sheet

Using VLOOKUP from another sheet follows the same method as above. The only difference is specifying the sheet name before the range. For example:

=VLOOKUP(105, 'Employee Data'!A2:D10, 3, FALSE)

This fetches data from the Employee Data sheet.

HLOOKUP Formula in Excel

The HLOOKUP function is similar to VLOOKUP but works horizontally instead of vertically. Its syntax is:

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

For example, if we have:

ID	101	102	103	104
Salary	50000	60000	70000	55000

The formula to find the salary for ID 103 is:

=HLOOKUP(103, A1:E2, 2, FALSE)

XLOOKUP Function in Excel

The XLOOKUP function is a modern alternative to VLOOKUP, offering more flexibility. Its syntax is:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

For example:

=XLOOKUP(103, A2:A5, D2:D5, "Not Found")

This searches 103 in column A and returns the corresponding salary from column D. If not found, it returns “Not Found” instead of an error.

VLOOKUP Formula PDF

For further reference, you can find detailed guides and examples in VLOOKUP formula PDFs available online. Many Microsoft Excel manuals provide downloadable PDFs explaining how to use VLOOKUP efficiently.

Conclusion

The VLOOKUP function in Excel is an essential tool for data lookup and retrieval. It allows users to efficiently find specific values in large datasets, whether within the same sheet or across multiple spreadsheets. Additionally, understanding related functions like HLOOKUP and XLOOKUP enhances Excel proficiency. By mastering VLOOKUP, users can significantly improve their data management and analytical skills in Excel.