Everything happens automatically within a few seconds. This is how the drop down list in Excel with the function of the VLOOKUP. In a cell G2, enter the formula (formula different column number 3):.Function has acquired the following form: The table is a range of materials with names and prices. The first argument is «Lookup_value» – the box with drop-down list.Open the window «Insert Function» and select VLOOKUP.Put the cursor in the F2 cell (which will have to appear the price). Now you need to make the way when choosing a particular material in the column price corresponding figure appeared. When press the OK the dropdown list is formed.Source is the range with the names of materials. Put the cursor in cell F3, where will be this list and go to the tab «DATA».Go to sheet «price list» and create a table for requests in the range E1:G2.It is necessary to configure the function so that when choosing names appeared prices. What data to take – column number 4 in the range A2:D15.įor example, some data is made in the form of a drop-down list.Where to look for – range of data A2:D15.What we are looking for – request the addition of a criterion F3&G3.Put the cursor in the right place H3 and set arguments for function:.We combine the required criteria the same way:.Adding to the table the leftmost column (important!), combining the «Provider» and «Materials».The matter is complicated by the fact that one supplier receives several names. It is necessary to set two conditions to search the name of the material and the vendor. Guessing we need to find, what is the price of the corrugated cardboard of JSC «Magnet». In practice, it often requires several bands to compare and select the data value 2, 3 and other criteria. Until now, we have offered for the analysis of only one condition - the name of the material. The function VLOOKUP in Excel with multiple conditions We can find the numeral and percentage difference. The data presented in this way, can be compared. Then take the data from the second column of the new price (Per unit price) and substitute them in cell C2 under «NEW Price». This means that you need to take the name of the material from the range A2: A15, look it up in the «new prices» in column A. Select the first cell and select the function VLOOKUP.In the old price list do the column «NEW Price».We need to compare the old prices with the new prices. The function helps to compare the values in large tables. Only values remain.Ī quick comparison of the two tables with the help of VLOOKUP Still selected use the right mouse button – «Paste Special».Select the column with inserted prices.If you change the price, and then change the value of the materials received at the warehouse (now received). Now to find the cost of materials is not difficult: the number of * cost.įunction VLOOKUP linked the two tables. And then copy the function of that column: we catch a mouse bottom right corner and drag down. As we need specific, not approximate.Ĭlick OK. Here are the data that you need to get in the first table. In the argument «Col_index_num» put the number «2».Select «Table_array» field value and press F4. For Excel to refer directly to these data, it is necessary to fix the link.Show what value the function should match. Select the range with the names of materials and prices. Put the cursor in the field of argument than go to the list prices. These are the values that Excel should find in the second table. In the field «Lookup_value» - the first column of the range of data from the table with the number of received materials. A window with the function arguments will be opened.This function can be called up by clicking on the «FORMULAS» tab and choose from the drop-down list of «Lookup & Reference». In the category «Lookup & Reference» we find the function =VLOOKUP(), and click OK. Call the «Insert Function» using the «FX» button (at the beginning of the formula bar) or by pressing the hot key combination SHIFT + F3. Select the first cell in the «Price» column.Establish a currency format for the new cells. Add columns «Per unit price» and total «Price». Here is the first table (for the warehouse) in the required view.And by ordinary multiplication we will find the title. To do this we should substitute the price of the second table in the first. It is necessary to find out the cost of materials received at the warehouse. The cost of materials is in the price list. For example, the warehouse of the enterprise for the production of packaging received materials in a certain amount.
0 kommentar(er)
