Use Excel vlookup function

Document directory

• Description

VLOOKUP (C2, Sheet3! $ B $1: $ C $35,2, FALSE). Pay attention to the usage of "$". When you drag it with your mouseTable_array
It will not change. I used it to process the data for a day. I haven't written a blog for a long time, indicating that I have never learned new things .......

The following is microsoft's official help:

 

 

Search for the value in the first column of the table array, and then return the value of other columns in the current row of the table array.

V in VLOOKUP indicates the vertical direction. When the comparison value is in the column on the left of the data to be searched, you can use VLOOKUP instead of HLOOKUP.

Syntax

VLOOKUP
(Lookup_value
,Table_array
,Col_index_num
, Range_lookup)

Lookup_value
An array is a table 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 .)


The value in the first column. Lookup_value
It can be a value or a reference. If lookup_value is smaller than the minimum value in the first column of table_array, VLOOKUP returns the error value # N/.

Table_array
It is two or more columns of data. Use a reference to a region or a region name. Table_array
The value in the first column is the value searched by lookup_value. These values can be text, numbers, or logical values. Case Insensitive.

Col_index_num
Table_array
Column Number of the matching value to be returned. 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, and so on. If col_index_num:

• If the VALUE is less than 1, VLOOKUP returns an error VALUE # VALUE !.
• If the number of columns is greater than that of table_array, VLOOKUP returns an error value # REF !.

Range_lookup
It is a logical value and specifies whether the exact matching value or approximate matching value is desired for VLOOKUP:

• If it is TRUE or omitted, the exact or 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.

  Values in the first column of table_array must be sorted in ascending order; otherwise, VLOOKUP may not return the correct values. Available"Data"
  On the menu"Sort"
  Command, and then select"Increment"
  To sort these values in ascending order. For more information, see Default sorting order
  .

• If it is FALSE, VLOOKUP will only search for exact matching values. In this case, the values in the first column of table_array do not need to be sorted. If table_array
  If there are two or more values in the first column that match lookup_value, the first value is used. If the exact match value is not found, the error value # N/A is returned.

Description

• When you search for text values in the first column of table_array, make sure that the data in the first column of table_array does not contain leading spaces, trailing spaces, and inconsistent direct quotation marks ('or
  "), Bent quotation marks ('or"), or non-printable characters. In the above cases, VLOOKUP may return incorrect or unexpected values. For more information about functions used to clear text data, see text and data Functions
  .
• When searching for a number or date value, make sure that the data in the first column of table_array is not saved as a text value. Otherwise, VLOOKUP
  An incorrect or unexpected value may be returned. For more information, see convert numbers saved as text to numeric values
  .
• If range_lookup is FALSE and lookup_value is text, you can use wildcards and question marks (?) in lookup_value (?)
  And star number (*). Question marks match any single character; asterisks match any character sequence. If you want to find the actual question mark or Asterisk, enter the waveform character (~) before the character (~).