VLOOKUP Not Working. Causes and Solutions

Although it's very popular for its calculation and charting functions, Excel offers many other features for its users. One of them is the VLOOKUP function , which can make your work faster and easier. But you might run into problems with it and notice that VLOOKUP isn't working.

Here we'll explain why VLOOKUP isn't working , and what you can do when it happens. It's easy to understand and doesn't require much experience with the Microsoft editor.

What Is The VLOOKUP Function?

The search function called " VLOOKUP " is simply a predefined formula that can help you find information in lists or tables in Excel. It can make your tasks much more efficient, especially when there is a lot of content and manual searching can be time-consuming.

In general, this is a function that will allow you to search, through a key information, for other complementary information within your data set.

However, this is a tool that allows you to search only vertically, that is, along a column.

How the VLOOKUP Function Works

This function consists of four search parameters:

=LOOKUP(lookup_value, array_table, column_index_num, [lookup_range])

• The search value: This is the information you want to find and is therefore required. It should always be the “number 1” column in your list or table.
• The matrix table: Represents the range of cells where the information you have is, so it is also mandatory.
• The column index number: Here you will use the column number for which you want to find the “lookup value”. This information is mandatory.
• The search range: This is the only optional information, as it should only be used when you want to specify an exact or approximate match, with "FALSE" being used when you are looking for an exact match and "TRUE" if it can be approximate.

The VLOOKUP function does not consider column headings such as "A, B, C...". You must always count the columns and enter the corresponding number, counting from left to right, starting with the first column containing data.

This is a very important fact that you should not forget, as it leads to a lot of confusion and causes the formula to not work properly when applied.

You might also be interested in How to Make a Standings Table in Excel

How to Use the VLOOKUP Function

Now that you know when to use the function and how it is formed, let's see how to use it with an example. Imagine that you have a business. Since the products you offer are varied, the quantities and values ​​also vary.

Let's understand how to use VLOOKUP to find information about your stocks simply and quickly.

• Insert the information into the spreadsheet so that the first column with content is always your search base. In this case, it is through the “Product” that we will find the matches of “Quantity" or "Price".

• Outside the list, type your search in columns to the side. The cells should contain the title of what will be searched for and the title of what will be found.
• Now let's insert the formula.
• Position yourself in cell G4, and click on the “ tabFormulas".
• Then click on “Insert function".
• In the context box, you will find the section “Find a function". Write here "VLOOKUP".
• Then, click on “Accept".
• The four sections that you must fill out to complete the formula will appear.
  • In sought value, you must enter what you are looking for, which in our case is represented by cell G3.
  • Matrix search in, is the complete data table. In our case from B4 to D14.
  • Indicator Columns, is the number of the column you want the result for. You must count all the columns in the table. In our case, it is the “Price”, which would be number 3.
  • In Order, you must put “FALSE” if you need the exact result of the table, or “TRUE” if you just want an approximate value. In our case, we put “FALSE".
• Then click on “Accept".
• When you enter the function, you will see that an error called “#N/A”. Don't worry, this only happens because the cell “G3” is not filled and this is a required parameter.
• Your function is ready. To perform the search, enter in the cell “G3” any of the “Products"To those who want to know the"Price".

In our example, we want to know the price of " Rice ", giving us the expected result " 8,00 euros ".

Now that you know how to use VLOOKUP , you can change the columns to suit your needs. This tool will save you a lot of time in your daily tasks.

VLOOKUP Doesn't Work

VLOOKUP is a command in Excel that may not work correctly because it needs to combine several arguments. The function is useful for looking up values ​​in a table without having to search manually.

With it, it is possible, for example, to create a button to check the price of a product recorded in an inventory sheet, as we have shown you in the example above.

However, the function must complete correctly to avoid errors. Depending on how you type in the cell, you might get error codes such as “#N/A” , “#REF!” and “#VALUE!”.

Below, we explain how to solve the main problems with the VLOOKUP function in Excel.

1. “#N/A” error with argument “TRUE”

The "#N/A" error usually occurs when the range is entered incorrectly in the command, not matching the obtained value. To avoid this problem, the first column of the array must contain the same value that is being searched for.

When looking up the price of a product in an inventory spreadsheet, as in the example above, the command should bring as the first column of the range the one that brings the names of the products.

It is important to consider the original column and not necessarily the one in the price query button cell.

2. Error “#N/A” with argument “FALSE”

When the user uses the " FALSE " argument in VLOOKUP , the "#N/A" error is not actually an error. The message simply means that the lookup did not find the exact value they were looking for.

To consider approximate values, it's worth remembering that you must use the " True " argument. The "False" argument is only for exact matches; therefore, you must enter the data you are looking for correctly so that it gives the expected result.

3. Error “#REF!”

The "#REF!" error is easy to resolve, as it simply indicates an error in entering the column reference number. This number is the second-to-last argument of the command and indicates the column's order within the array that should be considered when performing the search.

To consider only the third column in the three-column array we saw above, you must enter "3" in the argument. If, by mistake, the user enters a different column value, Excel returns the error "#REF!".

To correct this, it is therefore sufficient to change the number to the correct value, within the number of columns marked in the matrix.

4. Error “#VALUE!”

The "#VALUE!" error can occur in two situations. One happens when the searched cell, listed first in the argument, contains more than 255 characters. Since the cause is usually a typo, simply go to the cell and correct the value.

Another situation again involves the column reference number. When typing the VLOOKUP command , the number indicating the column's position in the array must obviously be greater than zero. When the user accidentally enters "0", the cell displays the message "#VALUE!".

5. Error “#NAME?”

Excel throws the "#NAME?" error , stopping the VLOOKUP command, when the user uses a name as a search reference but forgets to enclose it in quotation marks.

The problem can be easily solved by adding the quotation marks before and after the name. Additionally, it is possible to select the cell where the name is located to avoid possible typos.

6. Error “#DUMP!”

Excel displays the error code "#DUMP!" when the user uses an entire column as a reference for the argument. VLOOKUP allows you to do this, but requires using an at sign (@) at the beginning to avoid problems, as in the format: =VLOOKUP(@A:A, A:C, 2, 0)

7. Incorrect value error

VLOOKUP can also return a completely incorrect value. This is not an error code, but unusual behavior due to an error in the array format.

This happens when the user uses the " TRUE " command to find approximate values ​​but forgets to organize the items in the first column. For approximate search to work, the first column must always be in alphabetical or numbered order.

It is also possible to get incorrect values ​​if the " FALSE " command is used but the search data is not sorted in ascending order.

To close

You might also be interested in How to Link a Drop-Down List in Excel

With the explanation of the function and its use, as well as the possible errors you might encounter, we believe you are ready to use the VLOOKUP function . It's not very difficult to use, provided you understand the concepts and the correct order of the formula when creating it.