LOOKUP Function

VLOOKUP Function

HLOOKUP Function

XLOOKUP Function

INDEX and MATCH

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])

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])

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])

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 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]))

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))