Excel Vsearch explained in 5 minutes

🤔 What is the VLookup function for?

Do you need to search for data in your sheet but don't want to waste hours doing it by hand? That's good, the Search V is there for that! It is one of the most useful Excel functions to increase productivity and the 3rd most used function in Excel after the SUM and AVERAGE functions.

It allows you to automatically find values in a spreadsheet containing a lot of data. It searches for and retrieves data in the first column of an Excel table and returns the value in a column in a table. The tables involved in the research will therefore respectively be called

Imagine, that you have a table containing the names, geographic addresses, telephone numbers, email addresses of 1000 employees in your company. To find a particular employee's email address, you won't need to search for their name by hand. The VLOOKUP function will return it directly to you. You save time and increase productivity. 💪

‍

🧰 How do I use the VLookup function?

The searchV function is composed of three main arguments and is therefore presented as:

VLOOKUP (search_value; matrix-table; no_index_col)

🎯 Seeked-value

Seeked-value is the value you are looking for. It should be in the first column of your chart in your matrix-table.

• The value you are looking for and the corresponding cell must also have the same format: A string “1” will not match the number “1”.
• The function is case insensitive, i.e. uppercase and lowercase letters. If you search for “key,” it will match “Key.”
• If the value you are looking for is present in several cells in the table at the same time, only the first value found will be selected.

📊 Matrix_table

Matrix_table is the data range where the search will be performed. It must contain the information you are looking for In its 1st column and extend over 2 columns at least.

🔢 Col_index_number

col_index_number is the index, that is to say the column number in which the data to be extracted is located. The column index is an integer. The function will not work if you remove, add, or move a column in the table.

✨ Near_value

This 4^th argument is optional. So we have: VLOOKUP (search_value; matrix; col_index_number; near_value).

near_value is an optional argument that tells Excel what to do if it can't find the sought_value in the table.

It can take two basic values: TRUE and FALSE which can also be replaced by 1 and 0 respectively.

TRUE, for an approximate match search. If Excel cannot find the key, it will return the lowest value that is close to it. This will only work if the table has been sorted in ascending order.

WRONG, for an exact match search. If Excel cannot find the key, it will return an error. FALSE is the base value to use to get an absolute match.

⚠️ By default the search is approximate, if you want to do a search Exact Remember to specify the argument near_value !

‍

📊 Example in pictures

To make it more clear, here is an example in pictures!

If you want to retrieve the employee's name with the ID 10004, you just need to use the searchV function (10004, A:B,2)

10004 is the employee ID

A:B Is the table containing the data

2 : is the column where the data we are looking for is stored

‍

‍

🛎️ Bonus

La Search V is only done vertically (V for vertical). It starts from the top row of the column and goes down. For a search in the other direction, use the function Search H (H for Horizontal). You can search in both directions and without needing to have the value you are looking for in the 1st column of the matrix table with the Search X.

‍

‍