Why is my VLOOKUP returning an error?

The most common cause of the #N/A error is with XLOOKUP, VLOOKUP, HLOOKUP, LOOKUP, or MATCH functions if a formula can’t find a referenced value. For example, your lookup value doesn’t exist in the source data. In this case there is no “Banana” listed in the lookup table, so VLOOKUP returns a #N/A error.

How do I stop VLOOKUP error?

Change the VLOOKUP function to look for an exact match. To do that, set the range_lookup argument to FALSE. No sorting is necessary for FALSE. Use the INDEX/MATCH function to look up a value in an unsorted table.

What are the two main causes of errors for VLOOKUP?

Solution: When using a VLOOKUP formula for more than one cell, always lock the table array reference with the $ sign like $A$2:$B$10. Another common reason for VLOOKUP failure is the difference between your lookup value and a similar value in the lookup column.

Is match better than VLOOKUP?

INDEX-MATCH is much better: It’s never slower than VLOOKUP and can be much faster. It returns a reference rather than a value, which allows us to use it for more purposes. It doesn’t care where the result array is with regard to the lookup array.

How do you keep a VLOOKUP constant?

Locking the VLOOKUP

1. An Absolute Reference can be created by typing a “$” in front of either the row or column of a cell reference.
2. We can take this same approach to a set of data by creating Absolute References to the starting and ending cells of the table array.

What are the disadvantages of VLOOKUP?

VLOOKUP disadvantages: only provides data in a column. a single value must be in the row. data types should be the same in both columns. cannot lookup values from the left.

Why is my VLOOKUP not working with numbers?

To make the VLOOKUP formula work correctly, the values have to match. If the problem is caused by text numbers in one place, and real numbers in another, do the following to fix the problem: convert the real number to text, so both values are text. or, convert the text value to a number, so both values are numbers.

What is better than VLOOKUP?

The superior alternative to VLOOKUP is INDEX MATCH. While VLOOKUP works fine in most cases, it tends to reveal flaws when you start using it in large and complex sheets. The INDEX MATCH formula is actually two different functions; INDEX and MATCH.

What happens to a VLOOKUP when the workbook is closed?

However when the “ref” workbook is closed, as soon as any new work is done on the “lookup” workbook, the (previously fine) vlookup returns a #REF value. When the “ref” workbook is reopened, the vlookups go back to returning the expected values.

How do you fix Excel VLOOKUP errors?

The first is to use the properly formatted data where there are no blank cells, values in incorrect format and remove the formulas. And another way is by using the IFERROR Function before VLookup. THE IFERROR function ignores the error returns anything in place of error, whatever we place for error.

Why does the VLOOKUP formula display the “value” error?

The VLOOKUP formula displays the #VALUE error if a value used in the formula is of the wrong data type. There can be two reasons for this #VALUE error: The lookup value should not be more than 255 characters.

How to avoid errors like “N/a” in VLOOKUP?

While we use the Vlookup function, there are some instances when we may get an error like #N/A. #Value and #Name, which commonly occur in Vlookup. To avoid such errors, there are 2 ways. The first is to use the properly formatted data where there are no blank cells, values in incorrect format and remove the formulas.