What if the VLOOKUP function returns an error value?

The VLOOKUP function returns the reason for the error value and the workaround.

I. VLOOKUP function BASIC syntax

=vlookup (lookup_value, Table_array,col_index_num, [range_lookup])

This is too complicated. Oh, look at the goose understand VLOOKUP function Chinese grammar bar:

=vlookup (lookup value, data source, first column, blur lookup 1/exact find 0)

　　Two. Examples to speak

Next, let's look at an application example of a VLOOKUP function.

Check inventory quantity by menu name.

The C11 cell formula is as follows:

=vlookup (B11, $B $: $D $6,2,0)

This use is familiar to everyone, but the VLOOKUP function is also not obedient, sometimes return error value #n/a. What the hell is going on here? Let's look at the causes and solutions:

First: There is no absolute reference to the data source.

When the formula is dragged, the lookup zone sends changes, causing the query value to be not found. So locking the query area is particularly important, otherwise you will not be able to query and return to #n/a.

Second: Specifies the third parameter error, and also returns an error value.

For example, the following formula

=vlookup (E11, $C $: $D $6,3,0)

The query area here is only C, d two columns, and the specified returned column is 3, clearly beyond the query area range, Excel is dizzy, so it will show #ref!

Third: The lookup value is inconsistent with the data in the data source.

1. There are spaces.

You can double-click a cell to see if the last character has a space after it or to see it in the formula bar.

Solution:

1 copy a data source and paste it into the search criteria for the formula.

2 Remove space directly through function trim

C11=vlookup (TRIM (B11), $B $: $D $6,2,0).

3 If the ERP system leads out of the data contains invisible characters, through the Clean function processing, general can be normal query.

Such as:

=vlookup (B11), $B $: $D $6,2,0)

2. Query values and query areas of the data type is not uniform, both text and values.

Can be judged by the type function.

Typy return information as follows:

value = 1; literal = 2; logical value = 4; error value = 16; array =64

In this case, you can query the text as long as you convert the number to a real number.

There are many ways to convert to numbers:

E11*1

E11/1

E11+0

e11-0

--e11

VALUE (E11)

Choose one of these when you use it.

Fourth, there is no query value in the query area, so #n/a is displayed.

The =iferror formula, "", can be used to screen out the error values.