LOOKUP Function

VLOOKUP Function

HLOOKUP Function

XLOOKUP Function

INDEX and MATCH

### What Are LOOKUP Functions in Excel?

Excel's LOOKUP functions efficiently retrieve data based on criteria. These functions aid in the quick extraction of information from large databases and streamline data management.

=LOOKUP(lookup_value, lookup_vector, [result_vector])

### LOOKUP Function

Excel’s LOOKUP function finds values in a range or array. It has two types: array and vector.

Example: To find the region for the sales ID S001: Formula: =LOOKUP("S001", A1:A10, B1:B10)

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

### VLOOKUP Function

Excel's most-used function, VLOOKUP, searches for a value in the first column of a range and retrieves a corresponding value from another column in the same row.

Example: You have a list of employee IDs in column A and their corresponding salaries in column B. To find the salary of an employee with the ID E102: Formula: =VLOOKUP("E102", A1:B10, 2, FALSE)

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

### HLOOKUP Function

The HLOOKUP function searches the top row of a range for a specified value and retrieves a value from a different row within the same column.

Example: If you have a table with monthly sales data for different products and you want to find the sales for Product B in March: Formula: =HLOOKUP("Product B", A1:D5, 3, FALSE)

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

### XLOOKUP Function

XLOOKUP was designed to replace VLOOKUP and HLOOKUP, offering enhanced features that make it more powerful and versatile.

Example: To find the price of a product with code P202 across rows: Formula: =XLOOKUP("P202", A1:A10, B1:B10)

=INDEX(return_range, MATCH(lookup_value, lookup_range, [match_type]))

### INDEX and MATCH

The INDEX and MATCH functions together offer an alternative to VLOOKUP and HLOOKUP, allowing you to search for values both horizontally and vertically, enhancing versatility in Excel.

Example: To find the department of an employee named "John Doe" using the INDEX and MATCH functions: Formula: =INDEX(B1:B10, MATCH("John Doe", A1:A10, 0))