Shulou(Shulou.com)06/02 Report--

Vlookup function query error how to solve, I believe that many inexperienced people do not know what to do, so this paper summarizes the causes of the problem and solutions, through this article I hope you can solve this problem.

Error scenario 1: problem handling with no value to be queried in the original data

Case description: when the original data does not have the condition value that we want to query, at this time we query the data, and the result will be displayed as an error value. For example, we need to query Zhang Qiang's number, because there is no Zhang Qiang in the original data, so we can't find it.

Solution: when the original data does not have the value we are looking for, we can replace the wrong data value by replacing the wrong value with the iferror function.

Function formula:

= IFERROR (E4) (E4), 2))

Error scenario 2: handling the error problem with a small number of query values

Case study: when we enter a few or more bits of the query condition value, the query result will also have an error. For example, the name in the original data source is Li Siqiang, but when we enter it, it becomes Li Si, resulting in a lack of names, which leads to errors in the results of the query.

Solution: in order to avoid the situation that the query condition value is small or multi-bit, we can deal with it by matching the wildcard *, which represents that the query name has any value of Li Si.

Function formula:

= VLOOKUP (E4 & "*", $Bachel2Vuche Centro 10magentin 2pyr0)

Error scenario 3: problem handling when there are spaces in the data area

Case description: when there are spaces in the data element, we will also have data matching errors, such as there is a space in the middle of Wang Wu above and 2 spaces in front of the Internet bar. So there is an error in the data during the query.

Solution: for the problem of spaces, we just need to replace them. Press ctrl+F, select replace, replace the content click the spacebar, the replaced content remains the same.

Function formula:

= VLOOKUP (E4GHAY BLY2VOLING 10pl 2JEL 0)

Error scenario 4: error handling without $locking cell range

Case description: we found that Zhang San, which we are looking for, ranked the first name in the original data. When we did not lock the range of cells and dragged down, the range of the third parameter to be queried changed from the original B3:C10 to B5:C12, that is, it was offset by 2 lines.

Solution: just fix the third parameter, the range we need to query, B3:C10 with $.

Function formula:

= VLOOKUP (E4) Bamboo 3Rose Centro 10, 2, 0)

After reading the above, have you mastered how to solve the query error of vlookup function? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.