Application of VLOOKUP function in Excel

Excel is a use of Office software, is necessary for office staff. Sometimes in the use of Excel when the need to speed up the efficiency of the use of various functions, today small series to introduce the application of VLOOKUP function in Excel. A lot of people are quite unfamiliar with this function. What do you mean by first understanding the VLOOKUP function in Excel?

is the longitudinal lookup function, which belongs to a class of functions like lookup and HLOOKUP functions, VLOOKUP is a column lookup, and ultimately returns the value of the query sequence required for that column; hlookup are searched by rows. The syntax includes: VLOOKUP (Lookup_value,table_array,col_index_num,range_lookup) as shown in the figure:

Lookup_value parameter Description

Lookup_value is the numeric value you want to find in the first column of the datasheet. Lookup_value can be a numeric value, a reference, or a text string.

Table_array parameter Description

Table_array is the data table in which you want to find data. Use a reference to a range or range name.

Col_index_num parameter Description

Col_index_num is the column ordinal of the matching value to be returned in table_array. Col_index_num is 1 o'clock, returns the value of the first column of table_array, col_index_num to 2 o'clock, returns the number of table_array second column, and so on. If the col_index_num is less than 1, the function VLOOKUP returns the error value #VALUE!; If the col_index_num is greater than the number of table_array columns, the function VLOOKUP returns the error value #ref!.

Range_lookup parameter Description

Range_lookup is a logical value that indicates whether the function VLOOKUP lookup is an exact match or an approximate match. If TRUE or omitted, an approximate matching value is returned. That is, if no exact match is found, the maximum value less than lookup_value is returned. If range_lookup is False or 0, the function VLOOKUP looks for an exact match and, if it is not found, returns an error value #N/A.

Small series through a case to analyze the application of VLOOKUP function in Excel. For example: We want to extract 100003, 100004, 100005, 100007, 1,000,105 people in the A2:f12 area of the annual total sales, and the corresponding input into the i4:i8. The manual lookup of one one is tedious when the volume of data is large, so use the VLOOKUP function to complete the operation here. As shown in the figure:

First you enter "=vlookup" in cell I4, and Excel prompts 4 parameters. The VLOOKUP result is as shown in the figure:

The first parameter, obviously, we want to let 100003 correspond to is I4, here enter "H4,";

The second argument, where we enter the area we are looking for (absolute reference), that is, "$A $: $F $,";

The third argument, "Total year" is the sixth column of the region, so enter "6" and enter "5" to enter the last quarter of the project;

The fourth argument, because we have to find the work number accurately, so enter "FALSE", "0". Finally complete the final closing parenthesis ")", get the Formula "=vlookup (H4, $A $: $F $12,6,0)", fill the other cells with the fill handle to finish the lookup operation.