Questions about the use of the vlookup function

How to use:

First of all, enter "=vlookup(" in cell i4, then excel will prompt 4 parameters.

The first parameter, obviously, is that we want the 100003 to correspond to h4, and here we enter "h4,".

The second parameter, here we enter the area we are looking for (absolute reference), which is "$a$2:$f$12,".

The third parameter, "Total for the Year" is the sixth column of the region, so if you enter "6" here, you will enter the Q4 project.

The vlookup function is a longitudinal lookup function in Excel, which belongs to a class of functions with the lookup function and the hlookup function, which are widely used in work, for example, it can be used to check data.

Functions such as quick import of data between multiple **. The function is to search by column, and finally return the value corresponding to the query sequence required by the column; The hlookup counterpart is found by row.

vlookup syntax.

1 There are four parameters in parentheses, which are required. The last parameter range lookup is a logical value, and we often enter a 0 word, or false; In fact, you can also enter a 1 word, or true. What's the difference between the two?

The former represents a complete search, and if it cannot be found, it will return the wrong value n a; The latter first looks for the exact same, and then finds a very close value when it can't find it, and has to pass back the error value n a if it can't find it.

2 The lookup value is an important parameter, which can be a numeric value, a text string, or a reference address. The most common thing we often use is the reference address.

Here's how:Device: win7Software: ExcelVersion: 2007

1. Open Excel** and enter data in column A and column B cells.

Need to find the same data in column A and column B.

2. Enter the function formula in cell C1: =vlookup(b:b,a:a,1,0).

3. Then click enter, you can see the same data in column A corresponding to column B in cell C1.

4. After that, drag the cell filling formula down, you can see the same data can be displayed, and the error prompt of different data displays.

The vlookup function is a lookup reference function with four parameters, which are as follows:

The first parameter: the value to be found, that is, the value that needs to be found, can be directly text, number, cell;

The second parameter: the data table, which is the data region being searched (Note: The first column of the data table must contain parameter 1, otherwise it will be error).

The third parameter, the ordinal number of columns, is the number of columns in the data table of parameter 2, which must be greater than or equal to 1 or less than or equal to the integer value of the total number of columns in the data table.

The fourth parameter: matching conditions, there are two kinds of fuzzy matching and exact matching, which is an optional parameter, and the default is fuzzy matching.

The following example is to find the value of cell F2 and return the data in column 2, and the input formula is as follows:

=vlookup(f2,a:d,3,0)

Formulas and renderings.

Note: The first three parameters can be obtained by other function formulas.

Cells are named according to the position of the column and column they are in, for example, the address "b5" refers to the cell at the intersection of column "b" and row 5.

This can be achieved with the vlookup() function:

Parameter: lookup value is the value that needs to be found in the first column of the data table, which can be a numeric value, a quote, or a text string. A table array is a data table in which data needs to be found, and a reference to a region or region name can be used.

col index num is the column number of the matching value to be returned in the table array. When col index num is set to 1, the value in the first column of the table array is returned. col index num is 2, returns the value in the second column of the table array, and so on. Range lookup is a logical value that indicates whether the function vlookup returns an exact match or a near-dismantling quick match.

If true or omitted, the approximate matching value is returned, that is, the approximate matching value is returned, and if the exact matching value cannot be found, the maximum value less than the lookup value is returned; If the range value is false, the function vlookup will return the exact stool ridge match value. If it can't be found, an error value n a is returned.

1. Enter "=vlookup" in the unit tie-up grid to introduce the vlookup function. In the Interpolation Function dialog box, find the vlookup function in the drop-down list where you select a function, and click OK to enter the function parameter settings.

2. Enter four key values in the function. Click the blank box below with the mouse (indicating that the data will be directed into the **) and then select "Formula" to insert the function, and the dialog box for inserting the function will pop up.

3. Drag the silver cross next to the cell to fill the whole row or column. Then right-click and paste it selectively, select "Numeric Item", and make sure that no matter how you change the order of the two tables at this time, the data from the past will not change.