I believe that many partners will use the VLOOKUP function in Excel. So what should I do if an error value appears during operation? The solution tips are shared below.
1. The "#N/A" error usually occurs, indicating that the value of the first parameter cannot be found in the first column of the data table.
There are generally two options for handling this kind of error value:
1) Verify whether the search value is spelled incorrectly;
2) Change to wildcard query:
=VLOOKUP(A8&"*",A1:D5,2,0)
2. If the third parameter of the VLOOKUP function exceeds the maximum number of columns in the data table or is less than 1, a #REF! error will occur.
In this case, just modify the correct number of return value columns.
3. If the value you are looking for is not in the first column of the data table, an error value will also appear.
As shown in the figure below, the [employee number] information is in the second column of the data table, and the value in the first column is to be returned, so the formula returns an error.
In this case, it is recommended to move the [employee number] column to the first column of the data table, or use the INDEX+MATCH function, LOOKUP function, etc. to solve the problem.
For example, in the example above, the employee number is in the second column of the data table. If you need to query the name on the left, you can use
=LOOKUP(1,0/(B2:B8=A8),A2:A8)
4. If the value being searched is a number and an error value is returned, in most cases the format of the search value is different from the original data table value.
As shown below, the number "111" is entered in cell B8, while the text number stored in column A is different, so an error is returned.
There are two ways to deal with the error mentioned above:
1) Use the [Column] function to convert the text numbers in column A into numbers;
2) Add &"" to the first parameter of VLOOKUP to convert it into text.
The above explains the techniques for solving error values when using the VLOOKUP function in Excel. I hope friends in need can learn from it.
