Lookup functions are essential tools in Excel that allow you to search for specific data within a range and return corresponding values. This module will cover three primary lookup functions: VLOOKUP, HLOOKUP, and XLOOKUP.

VLOOKUP (Vertical Lookup) searches for a value in the first column of a table and returns a value in the same row from a specified column.

• lookup_value: The value you want to search for.
• table_array: The range of cells that contains the data.
• col_index_num: The column number in the table from which to retrieve the value.
• [range_lookup]: Optional. TRUE for an approximate match, FALSE for an exact match.

Suppose you have the following table:

To find the salary of the person with ID 2:

This formula searches for the ID 2 in the first column and returns the value from the third column, which is 60000.

• Incorrect Column Index: Ensure the col_index_num is within the range of the table_array.
• Range Lookup: If you need an exact match, always set [range_lookup] to FALSE.

HLOOKUP (Horizontal Lookup) searches for a value in the first row of a table and returns a value in the same column from a specified row.

• lookup_value: The value you want to search for.
• table_array: The range of cells that contains the data.
• row_index_num: The row number in the table from which to retrieve the value.
• [range_lookup]: Optional. TRUE for an approximate match, FALSE for an exact match.

Suppose you have the following table:

To find the name of the person with ID 2:

This formula searches for the ID 2 in the first row and returns the value from the second row, which is Jane.

• Incorrect Row Index: Ensure the row_index_num is within the range of the table_array.
• Range Lookup: If you need an exact match, always set [range_lookup] to FALSE.

XLOOKUP is a more flexible and powerful function that can search both vertically and horizontally. It replaces the need for VLOOKUP and HLOOKUP.

• lookup_value: The value you want to search for.
• lookup_array: The range of cells to search.
• return_array: The range of cells to return the value from.
• [if_not_found]: Optional. The value to return if no match is found.
• [match_mode]: Optional. 0 for exact match, -1 for exact match or next smaller, 1 for exact match or next larger, 2 for wildcard match.
• [search_mode]: Optional. 1 for search from first to last, -1 for search from last to first.

Suppose you have the following table:

To find the salary of the person with ID 2:

This formula searches for the ID 2 in the range A2:A4 and returns the corresponding value from the range C2:C4, which is 60000.

• Incorrect Arrays: Ensure lookup_array and return_array are of the same size.
• Match Mode: Choose the correct match_mode for your needs.

Given the following table: | A | B | C | |---------|---------|---------| | Product | Price | Stock | | Apple | 1.2 | 100 | | Banana | 0.5 | 150 | | Cherry | 2.0 | 200 |

Find the stock of "Banana" using VLOOKUP.

Solution:

This formula returns 150.

Given the following table: | A | B | C | |---------|---------|---------| | Product | Apple | Banana | | Price | 1.2 | 0.5 | | Stock | 100 | 150 |

Find the price of "Banana" using HLOOKUP.

Solution:

This formula returns 0.5.

Given the following table: | A | B | C | |---------|---------|---------| | ID | Name | Salary | | 1 | John | 50000 | | 2 | Jane | 60000 | | 3 | Doe | 55000 |

Find the name of the person with ID 3 using XLOOKUP.

Solution:

This formula returns Doe.

In this module, you learned how to use VLOOKUP, HLOOKUP, and XLOOKUP functions to search for and retrieve data in Excel. These functions are powerful tools for data analysis and management. Practice using these functions with different datasets to become proficient in their application.