VLOOKUP is one of the most useful Excel functions. It helps you search for specific information in a table. However, the function has some limitations, so Microsoft created XLOOKUP for Excel 365. Let’s dive into how VLOOKUP works and its key points.

How to Write the VLOOKUP Function

To write VLOOKUP, we must fill these 4 arguments

1. lookup_value: The value you want to find. It's necessarily a single cell.
2. table_array: The data where you will perform your search.
   • Here, you must select a range of cells
   • The lookup_value must be found in the first column (the first column is the searching key)
3. col_index_num: The column number of your table_array is where the value you want to return is.
4. range_lookup: The type research
   • Type FALSE for an exact match or TRUE for an approximate match.

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

Look at this example to understand how to use the VLOOKUP function and how to write it.

• We have a table with all the informations about the products our company sell; that's the reference table.
• The first column is very important because it must contain the value we look for.
• We want to lookup information according to the product ID
• For instance, we want to return the price of a product.
• The only way to do this task is to "call" our "reference table" with one "product ID"

The construction of the function is quiet simple

1. Select the cell with the Product ID to lookup for
2. Then, selection your reference table
3. Select the column index, here 4 (explanation of this point just after)
4. VERY important, the 4th argument must be FALSE.

Two important remarks

The third argument, col_index_num, is crucial. It tells Excel which column to return data from.

• If your reference table is in A, B, C column, using 3 as the third argument will return data from column C.
• But now, if your reference table is in F, G, H column, to return the value in column H, you must write 3, the third column of your reference table.
• Many new users of VLOOKUP will write 8, because H it's the 8th column in the Excel grid 😡 WRONG

The forth argument must be filled even if it is optional. And there is a big mistake to avoid

• The default type_search is approximate match (Value TRUE, 1, or missing). Examples how to use this option here.
• But 99,9% of the use of VLOOKUP is for an exact match (Value FALSE or 0)
• This is a big mistake with this function because if you don't fill the 4th argument, VLOOKUP will not return the value you want. Even if you have correctly write the first 3 arguments of the function.

💡You will find more explanations about the errors return by VLOOKUP in this article

Use Copilot or ChatGPT

It's important to understand the logic of the function. Because like that you can use AI to create your formula.

Here, I have written a prompt by explaining the logic of VLOOKUP according to a value in C13, and my reference table, return the price. And it works 😃👍

The new way to use VLOOKUP

As we have seen, VLOOKUP has some limitations, like the approximiate and exact match. But there is more trouble like perform a search on the left size of the column ID.

This is why the Microsoft developpers have created a new lookup function; XLOOKUP. These articles will show you to work with this function

• VLOOKUP Left Lookup Using INDEX or XLOOKUP
• Return a Value Between Two Values in a Range
• Search in 2 Columns with VLOOKUP & XLOOKUP