Vlookup function usage

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
Vlookup (lookup_value, table_array, col_index_num, range_lookup) can be written as follows:
Vlookup (data to be searched in the first column, the data table in which data needs to be searched. The column number of a column value must be returned. The logical value is true or false.
1. lookup_value is "data to be searched in the first column of the data table", which can be a value, a text string, or a 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 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 a 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.
If col_index_num is 1, the value in the first column of table_array is returned.
If 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.

 

 

Switch to Vlookup

Recently, I fell in love with Vlookup, and someone updated it. Because of its vulnerability, only repeated values can be returned. The following is a detailed description!
Vlookup (lookup_value, table_array, col_index_num, range_lookup)
Lookup_value is the value to be searched in the first column of table_array.
It can be a numeric value, reference, or text string. Note that the type must be consistent with the type of the first column of table_array.
Text is case-insensitive when searching for text. Wildcards "*" and "?" can be used.

Table_array is the data table in which data is to be searched.
You can use a reference to the region or region name, a constant array, and a calculated memory array.
When you reference a region, You can reference the entire column. Excel automatically determines the region to be used.
The first column of this parameter must contain the searched content. Other columns must contain the content to be returned. The column sequence number of the returned content is specified by the next parameter.

Col_index_num is the column number of the matched value to be returned in table_array.
For example, if the value is 1, the value in the first column of table_array is returned; if the value is 2, the value in the second column of table_array is returned, and so on.
If col_index_num is less than 1, The Vlookup function returns an 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 !.

Range_lookup is a logical value that specifies whether the Vlookup function returns exact or approximate matching.
If it is true or omitted, the approximate matching value is returned. That is, if no exact matching value is found, the maximum value less than lookup_value is returned;
Approximate match queries are generally used for numeric queries. The first column of table_array must be sorted in ascending order; otherwise, the correct results cannot be returned.
If range_value is false (or 0), the Vlookup function returns the exact match value.
In this case, table_array does not have to be sorted. If no error is found, the error value # N/A is returned. You can use if to determine whether to remove the error message after ISNA detects the error.
========================================================== ==================================
Vlookup is often prone to errors # N/A. Below are several possibilities:
There are spaces or inconsistent data types.
You can use TRIM () to remove spaces before lookup_value.
If the format is inconsistent, You can forcibly convert the value to text. After lookup_value, it is an empty string expressed by & and.
Convert the text to a numeric value. Then, perform the lookup_value * 1 operation.
========================================================== ==================================
Good results! I have done many unfortunate things before!