Excel VLookup & Hlookup Formulas

Top Microsoft  Excel Lookup Formulas

MS Excel Formula  Vlookup, Hlookup and Xlookup

VLOOKUP stands for Vertical Lookup, and it’s a popular Excel function used to search for a specific value in the first column of a table or range (known as the “lookup table”) and return a corresponding value from a specified column within the same row.

VLOOKUP is commonly used for tasks like retrieving data from a database, creating summary reports, or linking data from multiple tables.

Here’s the syntax for the VLOOKUP function:

Excel

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

lookup_value: The value you want to find in the first column of the table_array.

table_array: The range of cells containing the data you want to search through. This range should include the lookup column and the column from which you want to retrieve data.

col_index_num: This specifies the column number from which to return data. For example, if you want data from the third column in the table_array, you would put “3” here.

[range_lookup]: An optional argument. If set to TRUE (or omitted), it performs an approximate match (finding the closest value). If set to FALSE, it performs an exact match (finding an exact match only).

Here’s an example of how to use VLOOKUP:

Suppose you have a table of products and their prices:

You want to find the price of a Banana. You can use VLOOKUP for this:

=VLOOKUP(“Banana”, A2:B4, 2, FALSE)

lookup_value: “Banana” – the value to find.

table_array: A2:B4 – the range of data to search.

col_index_num: 2 – retrieve data from the second column (Price column).

range_lookup: FALSE – perform an exact match.

The formula will return $0.75 as the result, which is the price of a Banana. It searches for “Banana” in the first column, finds it in the second row, and returns the corresponding value from the second column.

This is a simple example, but VLOOKUP is powerful for working with larger datasets or when you need to retrieve specific information based on a key value.

Hlookup definition with Example

HLOOKUP, or Horizontal Lookup, is a function in spreadsheet software like Microsoft Excel and Google Sheets that allows you to search for a specific value in the first row of a table or range of data and return a corresponding value from a specified row in that table or range.

It is particularly useful when you have data organized in rows and you want to find information associated with a specific header or label.

The syntax for the HLOOKUP function is as follows:

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

Here’s a breakdown of each argument:

lookup_value: This is the value you want to search for in the first row of the table_array. It is the criteria that the HLOOKUP function uses to find a match.

table_array: This is the range of cells that contains the data you want to search through. The first row of this range should contain the labels or headers, and the data you want to retrieve should be organized in subsequent rows.

row_index_num: This argument specifies the row number in the table_array from which you want to return a value. For example, if you want to retrieve data from the second row of the table_array, you would use 2 as the row_index_num.

[range_lookup] (optional): This argument controls whether you want an exact match or an approximate match. If you omit this argument or set it to TRUE (or leave it blank), HLOOKUP will search for an approximate match, which can be useful when dealing with sorted data. If you set it to FALSE, HLOOKUP will look for an exact match.

Here’s an example to illustrate how HLOOKUP works:

Suppose you have a table in Excel like this:

You can use the HLOOKUP function to find the age of a person with a specific ID. For instance, if you want to find the age of the person with ID 102, you can use the following HLOOKUP formula:

=HLOOKUP(102, A1:D4, 2, FALSE)

In this example:

102 is the lookup_value.

A1:D4 is the table_array.

2 is the row_index_num, which corresponds to the second row where the ages are located.

FALSE specifies an exact match.

The result will be 30, which is the age associated with ID 102.

Xlookup definition with Example

“XLOOKUP” is a function in Microsoft Excel used for searching and returning values from a table or range based on specified criteria.

It is a powerful and versatile lookup function introduced in Excel 365 and Excel 2019, and it can replace various other lookup functions like VLOOKUP, HLOOKUP, INDEX MATCH, and more.

Here’s a basic definition and an example of how to use the XLOOKUP function:

Definition: XLOOKUP searches for a value in one range or array and returns a corresponding value from another range or array, with the option to handle errors more gracefully compared to some other Excel lookup functions.

Example:

Suppose you have a list of products in one column and their corresponding prices in another column. You want to find the price of a specific product (let’s say “Apples”) using XLOOKUP.

Assuming your data is in cells A1:A5 for products and B1:B5 for prices, you can use the following XLOOKUP formula in Excel:

=XLOOKUP(“Apples”, A1:A5, B1:B5)

This formula will search for “Apples” in the range A1:A5 (the list of products) and return the corresponding price from range B1:B5. If “Apples” is found in the list, it will return the price; otherwise, it will return an error if the product is not found.

XLOOKUP also allows you to specify optional arguments, such as what to return if the value is not found or if multiple matches are found. It’s a versatile function for various lookup tasks in Excel.