Hello dear reader!
In this article, I want to describe probably one of the most useful functions in Excel - the VLOOKUP function. A lot can be said about a function, but the very first thing worth noting is that the function is one of the most complex and least understandable.
In this article I will try to describe in a simple and accessible language how the VLOOKUP function works, as well as show its features, description, and syntax with examples.
So, what exactly is this function, what does it do, and how is it written. The simplest explanation, about the work of the VLOOKUP function, it searches in the list of values, according to the criterion you specified, for a unique identifier, certain information that is associated with it.
If you decipher the name of the VLOOKUP function, then with the first letter it will become clear how this function works, B means "Vertical", that is, it looks for values \u200b\u200bin, but for horizontal lists we will have. For the first time, the VLOOKUP function became available to us with Excel 2000. 
The VLOOKUP function in Excel has the following syntax:
\u003d VLOOKUP (_search value _; _ table_; _ column number_; _ [interval view] _)where:
- lookup_value - this is exactly the value that we need to search for, and it can be any value: number, date, text, reference to a cell that contains the desired value or the value obtained by another formula;
- table - these are two or more columns with various data, by the way, the function does not take into account the case of characters in the search;
- column_number - this is the number of the column in the specified range, from which the value will be obtained, which is in the found row;
- interval_view - this parameter determines what exactly we will look for; for an exact match, the argument will be equal "FALSE" or an approximate match, the argument becomes TRUE... This parameter is optional, but important nonetheless. In the examples below, I'll demonstrate how to create exact and approximate match formulas.
Now, using an example, let's look at how to search for some data on another sheet, since in practice, the VLOOKUP function is very rarely used to search on the current sheet:
VLOOKUP ("GM"; $ A $ 5: $ B $ 10; 2)
The formula looks for the text "GM" in column A on the current sheet. 
Advice!
When using the "table" argument, it is desirable to use an option such as (this is the cell address with the $ sign). In this case, the search range will become fixed and will not change when you copy the formula.
Let's look at an example of finding values, how the VLOOKUP function works in another workbook:
VLOOKUP ("GM"; [Path to file] Base! A2: B10; 2)
As you can see, nothing complicated, the argument just got complicated. "table", now a link to a file with its name is attached to it.
Advice! It is better to enter a formula with two files included, on one you enter the VLOOKUP function, and when you get to the "table" argument, go to the second file and select the range you need to search with the mouse.
You can also consider an example when we do not know the exact value, we will use wildcard characters in the VLOOKUP formula. Sometimes we need to find not something specific, but something that has common characteristics, as an option we have many sofas of different models, like other furniture, but we only need to choose them.
The VLOOKUP function, when substituted, can use the following characters:
- "?" (question mark) - allows you to replace any one character;
- "*" (Asterisk) - will replace any number and sequence of characters.
VLOOKUP (“A *”; $ A $ 2: $ B $ 10; 1; FALSE)
Advice!
In order for the VLOOKUP function to work correctly, you need to use the "FALSE" parameter as the fourth argument.
Well, if we have already touched on the topic of exact or approximate match in the syntax of the VLOOKUP function, then let's take a closer look at it:
- if the argument "Interval view" is equal "LYING", in which case the formula searches for an exact match with the lookup value argument. If the formula encounters two or more values \u200b\u200bthat correspond to the "required value" argument, then the first from the list will be selected; in the case when no match is found, the formula will return;
- if the argument "Interval view"has the meaning "TRUE" the formula will look for an approximate match, more precisely, the function will first look for an exact match, and only then, not finding it, will select an approximate one.
Advice! In the case when the argument "interval view" equals "TRUE" or is not specified, the value in the first column is needed, from smallest to largest. Otherwise, there is a possibility of an erroneous result from the VLOOKUP function.
Let's consider how the VLOOKUP function works when an exact search is performed. For example, let's try to find which car is moving at 200 km / h. I think this formula will not be difficult for you:
\u003d VLOOKUP (200, $ A $ 2: $ B $ 15, 2, FALSE)
Despite the fact that we have several 200 values, we got only one, since if the VLOOKUP function matches exactly, the system uses only the first value that was found in the specified range. 
Now let's test how the VLOOKUP function works for an approximate match of values. Let's look for which car drives at 260 km / h. The first thing you do when "Interval view" equals "TRUE" - you are sorting your range of values \u200b\u200bby the first column in ascending order. This is necessary and important, since the VLOOKUP function finds the next largest value from a given condition, and then the search stops. If you don't follow the sorting advice, the result will be a message about or other strange results. 
To search, we use the following VLOOKUP:
\u003d VLOOKUP (260; $ A $ 2: $ B $ 15; 2; TRUE)
So, our formula returned the found value 240, although we also have a value of 270 and this value seems to be closer, but the peculiarity of how the VLOOKUP function works is that when using an exact match, it looks for the largest value in the list, but not exceeding him. 
I think the examples that I looked at in the article will help you understand everything about how the VLOOKUP function works, how it is used, and what it should be used for.
There are many more advanced examples though, which we'll cover in other articles.
And that's all for me! I really hope that all of the above is clear to you. I would be very grateful for the comments left, as this is an indicator of readability and inspires to write new articles! Share what you read with your friends and like it!
are some of the most useful functions in Excel.
Note: The Lookup Wizard feature is no longer available in Excel.
Here "s an example of how to use VLOOKUP.
\u003d VLOOKUP (B2, C2: E7,3, TRUE)
In this example, B2 is the first argument-an element of data that the function needs to work. For VLOOKUP, this first argument is the value that you want to find. This argument can be a cell reference, or a fixed value such as "smith" or 21,000. The second argument is the range of cells, C2-: E7, in which to search for the value you want to find. The third argument is the column in that range of cells that contains the value that you seek.
The fourth argument is optional. Enter either TRUE or FALSE. If you enter TRUE, or leave the argument blank, the function returns an approximate match of the value you specify in the first argument. If you enter FALSE, the function will match the value provide by the first argument. In other words, leaving the fourth argument blank-or entering TRUE-gives you more flexibility.
This example shows you how the function works. When you enter a value in cell B2 (the first argument), VLOOKUP searches the cells in the range C2: E7 (2nd argument) and returns the closest approximate match from the third column in the range, column E (3rd argument).
The fourth argument is empty, so the function returns an approximate match. If it didn "t, you" d have to enter one of the values \u200b\u200bin columns C or D to get a result at all.
When you "re comfortable with VLOOKUP, the HLOOKUP function is equally easy to use. You enter the same arguments, but it searches in rows instead of columns.
Using INDEX and MATCH instead of VLOOKUP
There are certain limitations with using VLOOKUP-the VLOOKUP function can only look up a value from left to right. This means that the column containing the value you look up should always be located to the left of the column containing the return value. Now if your spreadsheet isn "t built this way, then do not use VLOOKUP. Use the combination of INDEX and MATCH functions instead.
This example shows a small list where the value we want to search on, Chicago, isn "t in the leftmost column. So, we can" t use VLOOKUP. Instead, we "ll use the MATCH function to find Chicago in the range B1: B11. It" s found in row 4. Then, INDEX uses that value as the lookup argument, and finds the population for Chicago in the 4th column (column D). The formula used is shown in cell A14.
For more examples of using INDEX and MATCH instead of VLOOKUP, see the article https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ by Bill Jelen, Microsoft MVP.
Give it a try
If you want to experiment with lookup functions before you try them out with your own data, here "s some sample data.
VLOOKUP Example at work
Copy the following data into a blank spreadsheet.
Tip: Wrap Text (Home tab, Alignment group).
|
Viscosity |
Temperature |
|
|
Description |
||
|
VLOOKUP (1, A2: C10,2) |
Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A which is 0.946, and then returns the value from column B in the same row. |
|
|
VLOOKUP (1, A2: C10,3, TRUE) |
Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0.946, and then returns the value from column C in the same row. |
|
|
VLOOKUP (0.7, A2: C10,3, FALSE) |
Using an exact match, searches for the value 0.7 in column A. Because there is no exact match in column A, an error is returned. |
|
|
VLOOKUP (0.1, A2: C10,2, TRUE) |
Using an approximate match, searches for the value 0.1 in column A. Because 0.1 is less than the smallest value in column A, an error is returned. |
|
|
VLOOKUP (2, A2: C10,2, TRUE) |
Using an approximate match, searches for the value 2 in column A, finds the largest value less than or equal to 2 in column A, which is 1.29, and then returns the value from column B in the same row. |
HLOOKUP Example
Tip: Before you paste the data into Excel, set the column widths for columns A through C to 250 pixels, and click Wrap Text (Home tab, Alignment group).
|
Bearings |
||
|
Description |
||
|
HLOOKUP ("Axles", A1: C4, 2, TRUE) |
Looks up "Axles" in row 1, and returns the value from row 2 that "s in the same column (column A). |
|
|
HLOOKUP ("Bearings", A1: C4, 3, FALSE) |
Looks up "Bearings" in row 1, and returns the value from row 3 that "s in the same column (column B). |
|
|
HLOOKUP ("B", A1: C4, 3, TRUE) |
Looks up "B" in row 1, and returns the value from row 3 that "s in the same column. Because an exact match for" B "is not found, the largest value in row 1 that is less than" B "is used: "Axles," in column A. |
|
|
HLOOKUP ("Bolts", A1: C4, 4) |
Looks up "Bolts" in row 1, and returns the value from row 4 that "s in the same column (column C). |
|
|
HLOOKUP (3, (1,2,3; "a", "b", "c"; "d", "e", "f"), 2, TRUE) |
Looks up the number 3 in the three-row array constant, and returns the value from row 2 in the same (in this case, third) column. There are three rows of values \u200b\u200bin the array constant, each row separated by a semicolon (;). Because "c" is found in row 2 and in the same column as 3, "c" is returned. |
INDEX and MATCH Examples
This last example employs the INDEX and MATCH functions together to return the earliest invoice number and its corresponding date for each of five cities. Because the date is returned as a number, we use the TEXT function to format it as a date. The INDEX function actually uses the result of the MATCH function as its argument. The combination of the INDEX and MATCH functions are used twice in each formula - first, to return the invoice number, and then to return the date.
Copy all the cells in this table and paste it into cell A1 on a blank worksheet in Excel.
Tip: Before you paste the data into Excel, set the column widths for columns A through D to 250 pixels, and click Wrap Text (Home tab, Alignment group).
|
Invoice Date |
Earliest invoice by city, with date |
||
|
\u003d "Atlanta \u003d" & INDEX ($ A $ 2: $ C $ 33, MATCH ("Atlanta", $ B $ 2: $ B $ 33.0), 1) & ", Invoice date:" & TEXT (INDEX ($ A $ 2: $ C $ 33, MATCH ("Atlanta", $ B $ 2: $ B $ 33,0), 3), "m / d / yy") |
|||
|
\u003d "Austin \u003d" & INDEX ($ A $ 2: $ C $ 33, MATCH ("Austin", $ B $ 2: $ B $ 33.0), 1) & ", Invoice date:" & TEXT (INDEX ($ A $ 2: $ C $ 33, MATCH ("Austin", $ B $ 2: $ B $ 33.0), 3), "m / d / yy") |
|||
|
\u003d "Dallas \u003d" & INDEX ($ A $ 2: $ C $ 33, MATCH ("Dallas", $ B $ 2: $ B $ 33.0), 1) & ", Invoice date:" & TEXT (INDEX ($ A $ 2: $ C $ 33, MATCH ("Dallas", $ B $ 2: $ B $ 33,0), 3), "m / d / yy") |
|||
|
\u003d "New Orleans \u003d" \u200b\u200b& INDEX ($ A $ 2: $ C $ 33, MATCH ("New Orleans", $ B $ 2: $ B $ 33.0), 1) & ", Invoice date:" & TEXT (INDEX ($ A $ 2: $ C $ 33, MATCH ("New Orleans", $ B $ 2: $ B $ 33,0), 3), "m / d / yy") |
|||
|
\u003d "Tampa \u003d" & INDEX ($ A $ 2: $ C $ 33, MATCH ("Tampa", $ B $ 2: $ B $ 33.0), 1) & ", Invoice date:" & TEXT (INDEX ($ A $ 2: $ C $ 33, MATCH ("Tampa", $ B $ 2: $ B $ 33,0), 3), "m / d / yy") |
|||
Let's say we have a table with a price list of goods. The task is to fill in the Orders table.
To solve this problem, we will use vLOOKUP function... In Excel, there are other functions with which you can solve this problem, but we will analyze it the most popular and frequently used VLOOKUP function in Excel.
So, to solve the problem in our example, we first need to fill in the column "FROM" in the order table, that is, find the price of goods in the "Price list" table, and then, to find out the cost, multiply the price by the number of goods.
The syntax for the VLOOKUP function in Excel
VLOOKUP (lookup_value, table, column_number, [range_view])
VLOOKUP looks for a value in the leftmost column of a table and returns the value of a cell in the specified column of the same row.
Let's analyze the function using our example. Let's insert rates in cell C3 and type the formula \u003d VLOOKUP (A3; $ F $ 2: $ H $ 22; 3; 0)
english version \u003d VLOOKUP (A3; $ F $ 2: $ H $ 22; 3; 0)
In this formula with VLOOKUP (English VLOOKUP):
A3 - lookup_value... In our case, this is the "Bucket", that is, we need to find the "bucket" in the "Price List" table
$ F $ 2: $ H $ 22 - table... In our example, this is the "Price List" table (F2: H22). In the range of this table, $ signs have been inserted to secure it so that it does not move down when we drag the formula. The dollar sign in Excel converts a relative range to an absolute range.
3 – column number... In our case, this is the number "3", since the price is in the third column of our "Price List" table.
0 - interval view. It can take only two values \u200b\u200b0 or 1: 0 - searches for an exact match, 1 - approximate. In 99% of cases, we need to look for the exact value (in our case, we need to look for the word "Bucket"). Therefore, the number 0 is almost always indicated.
In this way, excel VLOOKUP logic in our example the following. The function searches for the desired value ("bucket") in the leftmost column of the table ("Price List"), after finding it - returns the value of the cell located in the specified column same line, i.e. the price is 120 rubles.
After that, go to the cell D3 and finds the value of the goods. We write the formula \u003d C3 * B3, that is, we multiply the price of the product by the quantity.
Further, for automatic setting of formulas for the rest of the goods, you need to stretch the formula down. To do this, select both cells that need to be stretched and pulled down by the lower right corner (see Figure 3)
Many people know and often use it. But at the same time, it has two significant drawbacks, for example, how to make the "Left VLOOKUP". I use VLOOKUP itself only if I need to do something quickly. In files for "regular" use, I make the construction INDEX and SEARCH. Why is it better?
- When adding a column to the data table, you do not need to change the column number in the formula itself (as in VLOOKUP). The column will move automatically
- You can do the VLOOKUP the other way around, i.e. make a selection from the table from right to left. In VLOOKUP, the first column should always be searchable.
How to do all this, read below :)
I will use an example from. In order to better understand the application of this construct versus VLOOKUP:
How easy is it to fill out when you see the formula for the first time?
First, decide where the initial table is and where to get the data! Step by step what to fill.
- Insert the formula into the desired area of \u200b\u200bthe table
- In place of $ G: $ G put those cells in which the value should be found and, accordingly, should appear as a result. We are looking for the last name, so look for the last name column in the initial table.
- Replace $ J: $ J with depending on which values \u200b\u200bshould be returned to the cell. We need Surnames depending on the vehicle - we insert those vehicles around which the values \u200b\u200bshould appear.
- Instead of $ H: $ H, fill in the column with in which you need to find the corresponding value. Those. we are looking for the Surname by vehicle, which means we insert a column with the vehicle in the original table.
If you are wondering why the $ signs in the formula read
INDEX and SEARCH. What are these functions?
INDEX and SEARCH are very powerful functions that, in combination with others, give excellent results.
INDEX (array; row_num; column_num)
Returns the value at the intersection of the specified row and column of the specified range. Those. initially it works with two-dimensional arrays.
Selecting the data array J1: K4 and setting the row and column numbers to two, we got the corresponding value.
Looking at the original formula
INDEX ($ G: $ G; SEARCH ($ J: $ J; $ H: $ H; 0); 1)
We will see that instead of the second argument (line number) we have the formula SEARCH. What is she doing here?
SEARCH is a search by value. The function searches for a given value in a row or column and returns its ordinal number (from the beginning of the range). Those. in the second argument of the INDEX function, we find the vehicle number we need, get its number, for example 2.
And already in the one-dimensional array $ G: $ G we find the cell with row number \u003d 2. This will work for each cell in column J.
This is a seemingly not very complicated design, but as I wrote above it is very effective. Because you do not have to constantly change the number of the desired value, as in VLOOKUP, and you can search both to the right and to the left :)
Write comments if you still have questions.
As always!
Share our article on your social networks:Working with a summary table means pulling values \u200b\u200binto it from other tables. If there are a lot of tables, manual transfer will take a huge amount of time, and if the data is constantly updated, then it will already be a Sisyphean work. Fortunately, there is a VLOOKUP function that offers the ability to automatically fetch data. Let's take a look at specific examples of how this function works.
The name of the VLOOKUP function stands for "vertical view function". In English, its name sounds - VLOOKUP. This function searches for data in the left column of the range being studied, and then returns the resulting value to the specified cell. To put it simply, VLOOKUP allows you to rearrange values \u200b\u200bfrom a cell in one table to another table. Let's find out how to use the VLOOKUP function in Excel.
Example of using VLOOKUP
Let's take a look at how the VLOOKUP function works with a specific example.
We have two tables. The first is a purchasing table that contains the names of food products. The next column after the name is the value of the quantity of the product that you want to purchase. Next comes the price. And in the last column - the total cost of purchasing a specific product name, which is calculated using the formula for multiplying the quantity by the price already in the cell. But we just have to tighten the price using the VLOOKUP function from the next table, which is a price list.
As you can see, the price of potatoes has moved up to the table from the price list. In order not to carry out such a complicated procedure with other product names, we simply stand in the lower right corner of the filled cell so that a cross appears. We draw this cross to the very bottom of the table.
Thus, we pulled up all the necessary data from one table to another, using the VLOOKUP function.
As you can see, the VLOOKUP function is not as complicated as it seems at first glance. It is not very difficult to understand its use, but mastering this tool will save you a lot of time when working with tables.
