Syntax rules

Edit

The syntax rules for this function are as follows:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Arguments

Simple description

Input data type

lookup_value

value to lookup

numeric, reference, or text string

table_array

area to lookup

area of the table

col_index_num

Return data Number of columns in the lookup region

Positive integer

range_lookup

Fuzzy match/exact match

TRUE/FALSE (or leave it blank)

Parameter descriptions

Edit

Lookup_value is the value of the first column of the datatable table to be Lookup_value can be a value, a reference, or a text string. When the lookup value is omitted from the first parameter of the vlookup function, it means to lookup with 0.

Table_array for the need to find data in which the data table. Use references to regions or region names.

col_index_num for table_array? in the data to find the data column number. col_index_num for 1, return to the table_array first column of the value of col_index_num for 2, return to the second column of the value of table_array, and so on. If col_index_num is less than 1, the function VLOOKUP return error value # VALUE!; If col_index_num is greater than the number of columns of table_array, the function VLOOKUP return error value # REF!

Range_lookup for a logical value, specify the function VLOOKUP lookup is an exact match, or an approximate match. If FALSE or 0, an exact match is returned, and if none is found, an error value #N/A is returned; if range_lookup is TRUE or 1, VLOOKUP looks for an approximate match, i.e., if an exact match is not found, the largest value less than lookup_value? is returned. If range_lookup is omitted, it defaults to a fuzzy match. range_lookup is best specified explicitly and defaults to a fuzzy match!

Use Example

Edit

VLOOKUP Teaching Reference

As shown in the figure

VLOOKUP Function Example

Shown here, we are going to extract the total annual sales of the five people with job numbers 100003, 100004, 100005, 100007, and 100010 from the A2:F12 region. total sales for the whole year and input them into I4:I8 accordingly. One by one to manually find a large amount of data is very cumbersome, so the use of VLOOKUP function demonstration:

First of all, in cell I4, enter "= Vlookup (", at this time, Excel will be prompted by four parameters.

Vlookup results demonstration

The first parameter, obviously, we want to make 100003 corresponds to H4, here on the input "H4," ;

The second parameter, here to look for the region (absolute reference), that is, the input we want to find "$A$2:$F$12," ;

The third parameter, "Total for the year" is the sixth column of the region, so enter "6" here, and you'll get the fourth quarter's items. The fourth quarter of the project;

(Note: the number of columns here is not the default number of EXCEL columns, but to find the range of the first few columns)

The fourth parameter, because we want to accurately find the number of workers, so enter "FALSE" or "0".

The last complement to the last right bracket ")", the formula "= VLOOKUP (H4,$A$2:$F$12,6,0)", the use of fill handle to fill the other cells can be completed to find the operation.

VLOOKUP function notes

A. VLOOKUP syntax

1. There are four parameters in parentheses, is required. The last parameter range_lookup is a logical value, we often enter a 0 word, or False; in fact, you can also enter a 1 word, or true. what is the difference between the two? The former represents a complete lookup, can not be found on the return of the error value #N/A; the latter is first to find exactly the same, can not be found and then go to look for very close to the value, but also can not be found had to return the error value #N/A.

2. Lookup_value is a very important parameter, which can be a numerical value, text strings, or a reference address. We often use the reference address. With this parameter, there are three points to be specially reminded:

A) the reference address of the cell format categories and to search for the cell format of the category to be consistent, otherwise, sometimes see the information, is not to catch it. In particular, the value of the reference address is digital, the most obvious, if the search for the cell format category for the text format, although it seems to be 123, but it is not to catch things.

And format categories in the absence of input data to determine the first good, if the data are entered, found that the format does not match, it is too late, if you want to go to catch, you need to re-enter.

B) in the use of reference address, sometimes need to lookup_value value is fixed in a grid, and the use of drop-down (or copy) will be added to the function of the new cell to go, here we need to use the "$" symbol, which is a fixed role of the symbol. For example, I always want to grab the data in D5 format, you can make D5 like this: $D$5, then no matter how you pull, copy, the function will always grab the data with the value of D5.

C) with "&" to connect the contents of a number of cells as a parameter to find. In the case of finding data with similarities can be done with less effort.

3. Table_array is the search range, col_index_num is the number of columns in the range. col_index_num can not be less than 1, in fact, equal to 1 is no practical use. If there is an error like this with the value #REF!, it is possible that the value of col_index_num exceeds the total number of fields in the range. When selecting a Table_array be sure to note that the first column of the selection area must match the format and fields of the column selected by lookup_value. For example, lookup_value selected "name" in the "Zhang San", then Table_array selection of the first column must be "name" column, and the format with lookup_value. format is consistent with lookup_value, otherwise there will be #N/A problem.

4. In the use of this function, lookup_value value must be in the table_array in the first column.

II. VLOOKUP error value handling.

If you can't find the data, the function will always return an error value like #N/A, which is actually quite useful.

For example, if we want to do so to deal with: if you find, it will return the corresponding value, if you can not find it, it will automatically set its value equal to 0, then the function can be written as follows:

=if (iserror (vlookup (1,2,3,0)), 0, vlookup (1,2,3,0))

In Excel 2007 and above, the above formula is equivalent to

=IFERROR(vlookup(1,2,3,0),0)

The meaning of this sentence is: if the value returned by the VLOOKUP function is an error value, then (can not find the data), it will be equal to 0. Otherwise, it will be equal to the value returned by the VLOOKUP function (the corresponding value found).

Two more functions are used here.

The first is the iserror function. Its syntax is iserror(value), that is, to determine whether the value in parentheses is an error value, if so, it is equal to true, not, it is equal to false.

The second is the if function, which is also a commonly used function of the later have the opportunity to explain to you in detail. Its syntax is if (conditional judgment, result 1, result 2). If the conditional judgment is right, the implementation of the results of 1, otherwise the implementation of the results of 2. An example: = if (D2 = "", "empty", "there is something"), meaning that as D2 this grid is empty value, the display text "empty", otherwise, it shows "something". (looks simple, right? In fact, the program, this is how to judge to judge to go.)

In Excel 2007 or later, you can use iferror (value, value_if_error) instead of the combination of the above two functions, the function to determine whether the value expression is an error value, if it is, then return value_if_error, if not, then return the value of the value expression of its own value! .

Three. Handling of worksheet files containing the VLOOKUP function.

Generally speaking, worksheets containing VLOOKUP functions, if again in another archive to grab the data, the archive tends to be relatively large, especially when you use the archive itself is very large, then each time to open and save the disk is a very hurtful thing.

Is there any way to compress the file to speed up the opening and saving process? Here's a little experience.

In the worksheet, click Tools - Options - Calculation, remove the checkmarks for Update Remote References and Store External Links, and then save the file, which will speed things up quite a bit.

Below is a detailed description of how it works.

1. A worksheet containing the VLOOKUP function will save a copy of its external link every time it saves a file. So even if the worksheet is opened separately, the VLOOKUP function can capture the same values.

2. When a worksheet is opened, Microsoft prompts you to update the remote reference. Meaning, do you want to connect to the latest external file so that your VLOOKUP function can grab the latest values. If you're patient enough, give it a try.

3. Understanding this, we should know that each time we open a worksheet containing the VLOOKUP function alone, the value of the external file inside the grab, only the last time we saved the value of the disk. If you want to link to the latest values, you have to open the external file at the same time.

The most error-prone part of Vlookup is that the first column of the lookup area must contain the contents of the lookup.

Let's say you have a table where column a is the serial number, column b is the name, and column c is the ID card, and you enter one of the names in column d, and the formula to get its ID card in e1 can't be =vlookup(d1,a:c,3,0), but should be =vlookup(d1,b:c,2,0).

For the use of vlookup function, just look at the text is very boring, in order to facilitate understanding, you can view the reference material in the use of vlookup function examples to facilitate understanding and mastery.