It is very similar to the VLOOKUP but it is able to work well with arrays in the initial values. In other words, if in our table the values "pear" and "apple" are repeated, we cannot sum up all the pears and apples. It is worth noting that the main disadvantage of the VLOOKUP function is the inability to select several identical initial values in the query. You can execute the formula with a cyclic array only through a combination of hot keys: CTRL+SHIFT+ENTER.ĭownload example VLOOKUP function in two tables If you enter the curly braces manually in the formula string, this will not work. The entire process is performed cyclically thanks to an array of functions as indicated by the curly brackets in the formula bar. Now enter the name of the product in cell A11 and in cell B11 we get the sales amount in the first quarter for this product.Ĭomparison of two tables in Excel is performed by the VLOOKUP function and as soon as the matching of the requested data is determined, their values are immediately substituted for summation by the SUM function. Column index number: ", which indicates the execution of the formula in the array.To do this go to cell H3 and fill in its arguments as follows after calling the function: Now we need to make a selection of data using the VLOOKUP function separately for the product and sum up the sales for the first quarter.
There we will record the sales amounts in the first quarter as shown in the picture:Īs you can see, the second table also needs to be changed a little, so as not to lose the essence of the task. Expand the amount of data in the first table by adding columns: "January", "February", "March". Let's slightly complicate the task changing the structure and increasing the amount of data in the table. The VLOOKUP function in Excel and the two tables When a query is created to the database, the results are output which is a response to the query criteria. The function allows us to quickly find the data and get all the necessary values from the large tables. Now enter the names of the goods for which we need to know its price under the column headline of the second table "Name product". In the "Col_index_num" field we enter the value "2", because in the second column we have the price that we want to receive when searching for the goods. In the "Table_array" field enter the range of all values of the first table A3: B7. In the "Lookup_value" field, we enter a reference to the cell under the product name of the second table D3.
We move to the cell of the second table under the name of the column "Price".Nearby is another table, which will search in the first table using the criteria “name of the product” and get the value of the corresponding price. For example, if the table consists of two columns: "Product name" and "Price". The VLOOKUP function is used to retrieve data from an Excel table using certain search criteria. Select "Done" at the bottom of the formula window.How the VLOOKUP function works in Excel: an example
All together our formula looks like this: =VLOOKUP("Lizzo",A2:B16,2,FALSE).ġ1. We want our results to be spot on so we will write FALSE. If you want to save hours of research and frustration, try our live Excelchat service Our Excel Experts are available 24/7 to answer any Excel question you may have. Finally, enter whether you want the results to be approximate (TRUE) or exact (FALSE). Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. In our example, Instagram followers are represented in column B, so we would enter the numerical value of B, which is two (it's the second column).ġ0. Next, Excel wants you to enter the " column number in the range that contains the return value" - this data point should be where you want the data to come from. For this example, we want to search through all the data, encompassing all fields on the worksheet, so we'll enter "A2:B16."ĩ. Remember: VLOOKUP searches for data vertically, so be sure to enter the column letter followed by the row number. Now you will enter where Excel should search for the data - this can be two or more columns, creating a range on the lookup table. Enter the fields into the formula builder on the right.Ĩ.
0 kommentar(er)
