Vlookup function usage in Excel

Vlookup function usage

"Lookup" means "lookup" in Chinese. In Excel, there are three functions related to "lookup": Vlookup, hlookuo, and lookup. The following describes how to use the Vlookup function.

I. Functions
Search for the specified data in the first column of the table and return the data in the specified column in the row where the specified data is located.

Ii. Syntax
Standard Format:
Vlookup (lookup_value, table_array, col_index_num, range_lookup)

Iii. Syntax explanation
Vlookup (lookup_value, table_array, col_index_num, range_lookup) can be written as follows:
Vlookup (the data to be searched in the first column, the data table to be searched for, the column number of a column value, logical value true or false)
1. lookup_value is "data to be searched in the first column of the data table", which can be a value, text string, or reference.
2. table_array is "The data table in which data needs to be searched". You can use the cell region or region name.
(1) If range_lookup is true or omitted, the values in the first column of table_array must be listed in ascending order. Otherwise, the Vlookup function cannot return the correct values.
If range_lookup is false, table_array does not have to be sorted.
(2) the value in the first column of table_array can be text, number, or logical value. Text is case-insensitive.
3. col_index_num is the column number of the matched value to be returned in table_array.
When col_index_num is 1, the value in the first column of table_array is returned;
When col_index_num is 2, the value in the second column of table_array is returned, and so on.
If col_index_num is smaller than 1, The Vlookup function returns the error value # Value !;
If col_index_num is greater than the number of columns in table_array, the function Vlookup returns the error value # ref !.
4. range_lookup is a logical value, indicating whether the Vlookup function returns exact or approximate match. If true or omitted, the approximate matching value is returned. That is, if no exact matching value is found, the maximum value smaller than lookup_value is returned. If range_value is false, the Vlookup function returns the exact matching value. If no value is found, an error value # N/A is returned.

Iv. Application Example
A B c d
1 No. Name Wage Department
2 2005001 Jay Chou 2870 Office
3 2005002 xiaoyaxuan 2750 Personnel Section
4 2005006 Zheng Zhihua 2680 supply department
2005010 tu honggang 2980 Sales Section
6 2005019 sun Nan 2530 Finance Department
2005036 Meng Tingyi 2200 MEETING

Column A is sorted (the fourth parameter defaults to true)
Vlookup (2005001, A1: D7, 2, true) is equal to "Jay Chou"
Vlookup (2005001, A1: D7, 3, true) is equal to "2870"
Vlookup (2005001, A1: D7, 4, true) equals to "office"
Vlookup (2005019, A1: D7, 2, true) is equal to "Sun Nan"
Vlookup (2005036, A1: D7, 3, true) is equal to "2200"
Vlookup (2005036, A1: D7, 4, true) is equal to "work meeting"
Vlookup (2005036, A1: D7, 4) is equal to "work meeting"

If column A is not sorted and the correct result is obtained, the fourth parameter must use falae.
Vlookup (2005001, A1: D7, 2, false) equals to "Jay Chou"
Vlookup (2005001, A1: D7, 3, false) equals "2870"
Vlookup (2005001, A1: D7, 4, false) equals to "office"
Vlookup (2005019, A1: D7, 2, false) equals "Sun Nan"
Vlookup (2005036, A1: D7, 3, false) equals "2200"
Vlookup (2005036, A1: D7, 4, false) is equal to "work meeting"

5. Applications of true and false
For example, if you want to search for a person with a given number in a table with tens of thousands of records, if the number is sorted in ascending order, you can easily find this person. If the numbers are not sorted, you have to search from top to bottom.
This is also the case when you use Vlookup to search for data. When the first column is sorted and the fourth parameter is set to true (or indeed saved), Excel can easily find data with high efficiency. When the first column is not sorted and the fourth parameter is set to false, Excel searches from top to bottom, which is less efficient.

I think that to precisely search for data, because the computation speed of the computer is very fast, you can omit the sorting operation and use the fourth parameter to use false.

Hlookup

See

In a table or numeric array (array: used to create a single formula that can generate multiple results or operate on a group of parameters arranged in rows and columns. The array area shares a formula. An array constant is a group of constants used as parameters .) In the first row of the table or array, find the specified value, and then return the value at the specified row in the current column. When the comparison value is in the first row of the data table and you want to find the data in the given row below, use the hlookup function. When the comparison value is in the column on the left of the data to be searched, use the Vlookup function.

H In hlookup indicates "row ".

Syntax

Hlookup(Lookup_value,Table_array,Row_index_num, Range_lookup)

Lookup_value is the value to be searched in the first row of the data table. Lookup_value can be a value, reference, or text string.

Table_array is the data table in which data is to be searched. You can use a reference to a region or region name.

• The value of the first row of table_array can be text, number, or logical value.

• If range_lookup is true, the values of the first row of table_array must be sorted in ascending order:...-2,-1, 0, 1, 2 ,... , A-Z, false, true; otherwise, the hlookup function cannot provide the correct value. If range_lookup is false, table_array does not have to be sorted.

• The text is case insensitive.

• You can use the following method to sort values in ascending order from left to right: select a value, click Sort in the Data menu, and then click options ", click the "sort by rows" option, and then click "OK ". In the "sort by" drop-down list box, select the row option and click the "ASCENDING" option.

Row_index_num is the row number of the matched value to be returned in table_array. When row_index_num is 1, the value of the first row of table_array is returned. When row_index_num is 2, the value of the second row of table_array is returned, and so on. If row_index_num is smaller than 1, the function hlookup returns the error value # Value !; If row_index_num is greater than the number of rows in table-array, the function hlookup returns the error value # ref !.

Range_lookup is a logical value that specifies whether the function hlookup is exact or approximate. If it is true or omitted, an approximate matching value is returned. That is to say, if no exact match value is found, the maximum value smaller than lookup_value is returned. If range_value is false, the function hlookup will find the exact matching value. If not, an error value # N/A is returned !.

Description

• If the lookup_value cannot be found in the hlookup function and range_lookup is true, the maximum value smaller than lookup_value is used.
• If the function hlookup is smaller than the minimum value in the first row of table_array, the function hlookup returns the error value # N/!.

Example

If you copy an example to a blank worksheet, the example may be easier to understand.

Procedure

1. Create a blank workbook or worksheet.
2. Select an example in the help topic. Do not select the row or column title.

   Select an example from help.

3. Press Ctrl + C.
4. In the worksheet, select cell A1 and press Ctrl + v.
5. To switch between the query result and the formula for viewing the returned result, press Ctrl + '(heavy note) or point to "Formula Review" on the "Tools" menu ", click "Formula Review mode ".

1 2 3 4

A B C Axles Bearings Bolts 4 4 9 5 7 10 6 8 11 Formula Description (result) = Hlookup ("axles", A1: C4, 2, true) Search for axles in the first line and return the value of row 2nd in the same column. (4) = Hlookup ("bearings", A1: C4, 3, false) Search for bearings in the first row and return the value of row 3rd in the same column. (7) = Hlookup ("B", A1: C4, 3, true) Search for B in the first row and return the value of row 3rd in the same column. Because B is not an exact match, an axles smaller than the maximum value of B is used. (5) = Hlookup ("bolts", A1: C4, 4) Search for bolts in the first row and return the value of row 4th in the same column. (11) = Hlookup (3, {1, 2; 3; "A", "B", "C"; "D", "E", "F"}, 2, true) Search for 3 in the first row of an array constant and return the value of row 2nd in the same column. (C)