![]() ![]() 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. In Excel, you find controls to customize a pivot table in myriad places: the PivotTable Analyze tab, Design tab, Field Settings dialog box, Data Field Settings dialog box, PivotTable Options dialog box, and context menus. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |