The use method of analytic VLOOKUP function

1, the grammatical structure of the VLOOKUP function

The entire computer is the equivalent of a language, first of all, to familiarize itself with the grammatical structure of the function.

VLOOKUP (lookup_value, table_array, Col_index_num, [range_lookup]).

A more comprehensible expression is VLOOKUP (lookup value, lookup range, lookup column number, exact match, or approximate match)

In our work, we almost all use exact matches, and the parameters of the item must be selected as false. Otherwise, the return value will be unexpected.

　2, VLOOKUP function Use example

VLOOKUP is the vertical lookup, which is the column lookup. In layman's terms, depending on the lookup value parameter, search for a lookup value in the first column of the lookup range, and after the value is found, the return value is: whichever is the first, and backward to find the value for this column of the column value. This is why this function is called VLOOKUP (V for vertical-vertical meaning, lookup instant English)

The existing sales of the following mobile phone daily gross data (figure left), a distributor needs to provide four models of sales data (figure right)

3, this time, probably go back to a manual search, because the number of data provided is very small, but in fact the work of this data is very large, manual search is undoubtedly a waste of time, and can not let a distributor believe that you provide the accuracy of the data. Next, you will need the protagonist of this time. Use the VLOOKUP function.

Step One: Select the cells that you want to enter data in, =vlookup (H3, $A $: $F $19,5,false) as shown

4, the parameter explanation: H3 for wants to look for the model, namely iphone5. Why write H3, instead of writing iphone5 directly, because the formula is easy to drag and fill, and to ensure accuracy.

5, Parameter explanation: $A $: $F $19 for the need to do in this scope to find, why add absolute reference, because the following IP4 and the rest of the search refers to the area, that is, the data source, plus the absolute reference, you can directly drag the formula.

6, Parameter explanation: 5 from the first column of the data source, we want to query the number of 7/31 sales in the first column of reference (that is, the model column) after the fifth column.

Notice that the number of columns here is 1 from the first column of the reference range, rather than column A as the first column, and pay attention here.

7, parameter explanation: False in daily life, most of the use is accurate matching to the value I want to query, do not use true, the result of using true is to bring unexpected results.

8, enter, can get iphone5 in 7/30 of sales. All the other just drag and drop to get the complete data report. As shown in the simple test, the values are equal after viewing.