# Xlookup vs Vlookup: Brief Comparison Excel functions play a crucial role in simplifying complex tasks and making data analysis more efficient when working with data in spreadsheets. Two commonly used functions for searching and retrieving data are XLOOKUP and VLOOKUP. These functions are designed to find a value in a table and return a corresponding value from another column. As technology gets better, software tools like Microsoft Excel also get better. They add new functions that provide improved capabilities.

This article discusses What is XLOOKUP and VLOOKUP, their syntax and have done a brief comparison between Xlookup and Vlookup.

## VLOOKUP

VLOOKUP is short for Vertical Lookup. It has been a fundamental tool in Excel for many years. This function is used to search for a value in the leftmost column of a table and return a corresponding value from a specified column within the same row. VLOOKUP is widely employed for tasks such as retrieving prices, matching product IDs, and cross-referencing data sets.

### Syntax

=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.
• table_array: The range of cells containing the data you want to search.
• col_index_num: The column number from which you want to retrieve the corresponding value.
• range_lookup: An optional parameter that determines whether you want an exact or approximate match.

For Example: Suppose you have a list of products with their respective IDs and prices. You want to find the price of a product with ID 103. You can use VLOOKUP as =VLOOKUP(103, A2:C10, 3, FALSE).

In this example, the formula searches for 103 in the leftmost column A2:A10 and returns the corresponding price from the third column C2:C10 of the table.

The result is 20, as you can see in the sample worksheet the price for the product with ID 103 is 20.

## XLOOKUP

XLOOKUP is a newer tool to Excel’s functions. It takes data retrieval to the next level. The X in XLOOKUP signifies its ability to handle vertical and horizontal searches. This function allows you to search for a value in any column (not just the leftmost one) and retrieve a corresponding value from a specified column. Moreover, XLOOKUP can handle exact and approximate matches without requiring a separate parameter. The syntax of XLOOKUP is more flexible.

### Syntax

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

• lookup_value: The value you want to find within the `lookup_array`.
• lookup_array: The range where you want to search for the `lookup_value`.
• return_array: The range from which you want to retrieve the corresponding value.
• if_not_found: An optional parameter that specifies what should be returned if no match is found.
• match_mode: An optional parameter that determines the type of match (exact or approximate).
• search_mode: An optional parameter that specifies the search direction (first to last or last to first).

For Example: We will use the same example we used in the VLOOKUP. To find the price of the product with ID 103. You can use XLOOKUP as =XLOOKUP(103, A2:A10, C2:C10, “Not Found”, 0, 1).

In this example, the formula searches for 103 in the range A2:A10 and returns the corresponding price from the range C2:C10. If no match is found, it will return “Not Found.” The 0 in the match_mode parameter specifies an exact match, and the 1 in the search_mode parameter indicates searching from the first to the last.

The result is 20; as you can see in the sample worksheet, the price for the product with ID 103 is 20.

## A brief comparison between VLOOKUP and XLOOKUP #### Akash Singh

Akash is a law graduate who likes to go for bike rides on the weekends soul-searching for answers to his many existential questions. You can contact him here: [email protected]