Vlookup of Excel Functions

Vlookup Function
Find the specified value in the first column of the table or value array, and return the value at the specified column in the row of the value in the table or array.
The "array" mentioned here can be understood as a region in the table. Column Number of the array: "First column" of the array, which is the first column of the region. The right column of this column is 2nd columns and 3 columns in sequence ....... Assume that an array is B2: E10, so B2: B10 is 1st columns and C2: C10 is 2nd columns .......

Syntax:
Vlookup (search value, region, column number, logical value)
"Search value": the value to be searched in the first column of the array. It can be a value, reference, or text string.
"Region": the region where the array is located, such as "B2: E10". You can also use a reference to the region or region name, such as a database or data list.
"Column number": the column number of the value to be returned in the expected region (array). If it is 1, the value in the first column is returned. If it is 2, the value in the second column is returned, similarly, if the column number is smaller than 1, The Vlookup function returns the error value # Value !; If the number of columns exceeds the range, the function Vlookup returns an error value # ref !.

"Logical value": true or false. It specifies whether the Vlookup function returns exact or approximate match. If the value is true or omitted, the approximate matching value is returned. That is to say, if the exact matching value cannot be found, the maximum value smaller than the "Search value" is returned. If the "logical value" is false, the Vlookup function returns the exact matching value. If no value is found, an error value # N/A is returned. If "Search value" is text, "logical value" should generally be false. In addition:

· If the "Search value" is smaller than the minimum value in the first column of "region", the function Vlookup returns the error value # N/.
· If the function Vlookup cannot find the "Search value" and the "logical value" is false, the function Vlookup returns the error value # N/.
The following example describes how to use the Vlookup function.
Assume that the unit prices of wheat, rice, corn, peanut and other agricultural products are stored in sheet1:
A B
1 agricultural product name unit price
2. Wheat 0.56
3. Rice 0.48
4. Corn 0.39
5 peanut 0.51
.......................................
100 soybean 0.45
Sheet2 is the sales list. The configuration content is different each time: Enter the agricultural product name and quantity in sheet2, and then automatically generate the unit price and sales amount based on the data of sheet1. Set the following table to sheet2:

A B c d
1. Agricultural Product Name quantity unit price
2. Rice 1000 0.48 480
3. Maize 2000 0.39 780
.........................................................
Enter the formula in cell D2:
= C2 * B2;
Enter the formula in cell C2:
= Vlookup (A2, sheet1! A2: B100, 2, false ).
If it is expressed in a language, the system searches for the A2 value of table sheet2 in the first column of Table A2: B100 in Table sheet1. After finding the value, the system returns the value of Column 2nd in this row.
In this way, when the name entered in cell A2 of table sheet2 changes, the unit price in Table C2 automatically changes. Of course, if the single value in sheet1 changes, the corresponding value in sheet2 also changes.
Other cell formulas can be written by filling.
Precautions for using Vlookup Functions
Speaking of the Vlookup function, I believe everyone will use it and it is very skillful. However, there are several details that you should pay attention to when using them.
1. Vlookup syntax
The complete syntax of the Vlookup function is as follows:
Vlookup (lookup_value, table_array, col_index_num, range_lookup)
1. There are four parameters in brackets, which are required. The last parameter range_lookup is a logical value. We often enter 0 or false. In fact, we can also enter 1 or true. What is the difference between the two? The former indicates the complete search, and the returned error value # N/A is not found; the latter first looks for the same, and then finds the very close value, if no value is found, the error value # N/A is returned. This does not actually have any practical significance for us, but it just satisfies our curiosity. Interested friends can experience it.

2. lookup_value is a very important parameter. It can be a value, a text string, or a reference address. We often use a reference address. When using this parameter, there are two special reminders:
A) The cell format category of the reference address must be the same as that of the cell format to be searched. Otherwise, you may not be able to grasp the information clearly. Especially when the value of the reference address is a number, it is most obvious that if the format of the searched cell is text, although it seems to be 123, it cannot catch anything.

In addition, you must confirm the format type before entering the data. If all the data is input, it is too late to find that the format is not correct. If you want to capture the data, you need to re-enter it.
B) The second reminder is that it is a convenient and practical skill. I believe many people have known it for a long time. When using the reference address, we sometimes need to fix the value of lookup_value in a grid, and add the function to a new cell using the drop-down (or copy) method, "$" is used here, which is a fixed symbol. For example, if I always want to capture data in the D5 format, I can make D5 like this: $ d $5, no matter how you pull or copy it, the function always captures data with the D5 value.

3. table_array is the search range, and col_index_num is the number of columns in the range. Col_index_num cannot be less than 1. Actually, it is useless if it is equal to 1. If such an error occurs # ref !, It may be the total number of fields whose col_index_num value exceeds the range.

2. Handle Vlookup error values.
We all know that if no data is found, the function always returns an error value # N/A, which is also useful. For example, if we want to do this: if it is found, the corresponding value will be returned. If it cannot be found, I will automatically set its value to 0, then the function can be written as follows:

= If (iserror (Vlookup (,) = true, 0, Vlookup ))
This statement indicates that if the value returned by the Vlookup function is an error value (data cannot be found), it is equal to 0. Otherwise, it is equal to the value returned by the Vlookup function (that is, the corresponding value found ).
Two more functions are used here.
The first is the iserror function. Its syntax is iserror (value), that is, to determine whether the value in the brackets is an error value. If yes, it is true. If no, it is false.
The second is the IF function, which is also a common function and will be explained in detail later. Its syntax is if (conditional attention, result 1, result 2 ). If the condition criterion is correct, result 1 is executed; otherwise, result 2 is executed. For example: = If (d2 = "", "null", "something"), which means that D2 is an empty value in the lattice, the text "empty" is displayed. Otherwise, "something" is displayed ". (Looks simple, right? In fact, this is the way the program is judged .)

3. Process worksheet files containing Vlookup functions.
In general, if a worksheet containing the Vlookup function captures data in another file, the file is usually relatively large. Especially when you use a large file, it is very hurt to start and store the disk each time.

Is there a way to compress the file to speed up opening and saving the disk. Here is a small experience.
In the work table, click "Tools"> "options"> "computing" to remove the above update remote reference and storage external links, and save the files. This will accelerate a lot. If you don't believe it, try again.
The following describes how it works.
1. A worksheet containing the Vlookup function saves an external linked file each time the file is saved. In this way, the Vlookup function can capture numeric values even when the worksheet is opened separately.
2. When the worksheet is opened, Microsoft will prompt you whether to update the remote reference. This means you want to connect to the latest external file so that your Vlookup function can catch the latest value. If you have enough patience, try it.

3. When we understand this, we should know that each time we open a worksheet containing the Vlookup function separately, the value of the external file is captured, but the value saved during the last disk storage. To link the latest value, you must open the external file at the same time.