Index formula

[ExcelNewby]

Greetings. Please review attachment.

I have budget table with a variance formula between Budget and Actuals. The formula locates in cell C19, = (C4-B4)/B4. Every month I would have to go and manually change the 4(Jan) to a 5(Feb), 5 to 6(March) etc…Could someone help me write an Index formula that based on the month in cell B1 it would change the formula automatically?? I keep getting an error #REF! message. Harder than I thought.

Greatly appreciated.

ExcelNewby:

[daddylonglegs]

Try

=LOOKUP(B1,A4:C15)/LOOKUP(B1,A4:B15)-1

[ExcelNewby]

Thank you assisting me but your formula does not work. #N/A. And when I tried to change the B2 to B1 since that is where the reference cell locates, it gives me endless #######. I need the formula to return in percentage variance for that month.

Thanks again.

ExcelNewby

[daddylonglegs]

I haven't used B2 in that formula?

If I put that formula in C19 in the spreadsheet you attached then I get the result you show now, i.e. -3.9%. If you cahnge the date in B1, e.g. to February 1st then it will calculate the February variance, assuming you put in the figures for February.

Isn't that what you want?

[ExcelNewby]

You're right the formula does work for that cell but when I copied and pasted that formula to cell E19 it gives me #NA. I also have 2 questions. What is the difference between a VLookup and an INDEX function? Can the INDEX function works in this scenario?

Much gratitude

[broro183]

hi all,

I've been playing in your file before DaddyLonglegs replied so I have a couple of different options (offset & index) in the attached zip file.

The offset formulae can be shortened by excluding the optional arguments but I have left them in as defaults for you to compare. I find the best way to see how a function works is to type in the name eg "=offset(" then click on the "Fx" button at the left of the formula bar (it's in Excel 2003 & I think most of the earlier versions but I don't know about 2007). Once you have clicked on this Excel brings up a popup box & as you click in each field a brief description is stated at the bottom of the popup or for more detail there is usually a link to the Help files ("more help on this function").

Also, I've made some other slight changes which may help with clarity/appearance of the printed page & the changed cells are highlighted yellow with the occasional comment scattered around for good measure ;-)

hth
Rob

[daddylonglegs]

OK, yes, if you want to copy the formula along row 19 then you can use some strategically placed $ signs, i.e. use

=LOOKUP($B1,$A4:C15)/LOOKUP($B1,$A4:B15)-1

You can use INDEX and MATCH but I think LOOKUP is more efficient in this case and works well because you're matching a date against an ascending list of dates. Note: B1 can be any date in January for the formula to work.

Note: I also simplified your original formula from (C4-B4)/B4 to C4/B4-1 which doesn't give much improvement there but is more significant if you need to replace each instance of B4 or C4 with a LOOKUP formula....

[ExcelNewby]

Rob and Daddylonglegs-YOU BOTH ROCK!!:)

Thank you for sharing your brainpower!! I learned something today...Yeah!

ExcelNewby.

[broro183]

Pleased we could help - thanks for the feedback :-)

btw, Daddylonglegs has more knowledge than me so his lookup approach is probably the better one to go for (it's definitely shorter & more readable), hopefully though you find the page formatting useful too.

Rob