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. I know it is possible, being as I have seen it before in a pivot. When I place additional fields in my rows, this option is not made available automatically. My first first always has the automatic filter drop down so I can sort by A-Z, highest to lowest etc.
0 Comments
Leave a Reply. |