The underlying structure is the same: =VLOOKUP(B23,$B$4:$J$7,MATCH(D23,$G$4:$J$4,0)+5,FALSE) The idea is to change the return range to the right hand side.Ĭopy these two formulas down for revenues and costs. The complete formula for revenues: =VLOOKUP(B11,$B$4:$F$7,MATCH(D11,$B$4:$F$4,0),FALSE)Īdapt the formula for the costs. The last 0 indicates, that we are searching for the exact match. Instead of using a fixed value for the return column, you search for the current year with the MATCH formula in the original cell range. The formula is basically a VLOOKUP, searching for the product (here: ‘Product 1’ in cell B11) in the column B (given by the range $B$4:$F$7). Therefore you can use a two-dimensional lookup as shown on the picture on the right hand side. Method 1 – Formula: For each combination of product, type and year you need to get the correct value from the table above.Tip: To quickly add a pivot table to a new sheet, select a table, then choose Pivot Table in the toolbar. For the values you got two options: use a formula or an Excel add-in (well, besides copying each value manually): Create a pivot table Select a table or range of cells in your spreadsheet. Value: Now the actual values are still missing.Step 4: Get the correct value for each combination of ‘Product’, ‘Type’ and ‘Year’ with a formula.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |