Workplace Office|VLOOKUP function jump pit guide

Workplace Office|VLOOKUP function jump pit guide

01 Problem description

If there are traffic stars in the Excel function, it must be the VLOOKUP function. The first time I listened to this function, I just studied it for a while, and my brother told me that this function is terrible, and I was blown away by my brother. It's a pity that I was young and frivolous and didn't learn the essence of it.

Simply put, the VLOOKUP function is used to find and match data. For example, in the following case, you need to find the grade according to your student ID.

02 Simple to use

1. let's look at the usage parameters of the VLOOKUP function.


Simply put, it is who to find, where to find, who to find, accurate or fuzzy search.

① We search by student ID, so it is A9; ②Where to find, here is the entire table; ③Find grades, in the fourth column of the table, so it is 4; ④Exact and fuzzy search are 0 and 1, respectively. Available False and True.

03 Jumping pit guide

The VLOOKUP function is very easy to use, but it is maddening to report an error. Then this time I will publish a guide to jump pits, detailing the "seven deadly sins" of the VLOOKUP function.

① Low-level error #NAME? This type of error is mainly caused by low-level errors such as function spelling and can be completely avoided.


② The #VALUE! error can be avoided mainly because of the incorrect use of function parameters. We only need to remember the four parameters of the VLOOKUP function and use them correctly.

③ Out of range #REF! This is mainly because the reference data source is missing or out of the reference range.

④ Crashing #N/A There are relatively few errors reported in the front. The most crashing is the #N/A error, which is the biggest pitfall.

In fact, the meaning of this error is very simple, that is, no data was found.

We must note that the column to be matched and searched must be the first column in the query table. For example, in this case, we are looking for results by name, and the query form must be


5. Put the name as the first column.

Secondly, this kind of error will be reported if the format of the data cell does not match. The search here is in text format, but if the match is in digital format, an error will be reported.

In general, #N/A first consider whether the matched column is in the first column, and then make sure that the matching data is the same.

Reference: Workplace Office|VLOOKUP Function Jumping Guide-Cloud + Community-Tencent Cloud