INDEX Function in Excel

INDEX Function in Excel

Didn't find your answer?

I'm trying to use INDEX to bring in monthly budget figures into financial reports. VLOOKUP or HLOOKUP would require chnaging formulae each month whereas INDEX would require only a date change. Any ideas ?
William Parry

Replies (5)

Please login or register to join the discussion.

aw_logo_2019
By Accounting WEB
24th Jan 2001 10:17

The INDEX formula is fine for your purposes, but...
...you just need to break the problem down further. The INDEX function looks for row & column numbers, rather than, e.g., dates or account codes. This means you need to set up two extra ranges somewhere in you workbook: one being a list of, say, your month-end dates and another being your account codes from your table of budgets. Beside each column you need to simply give each list entry a number. For example, for your month-end dates, 30/04/01 = 1, 31/05/01 = 2, et seq.

You then need to create VLOOKUP formulae based on these new ranges, for example, at the top of your budget report, a formula to show which number in the list the month-end date is, i.e. the "Column Number" field required by the INDEX formula. By each of your account codes you need to do the same: a VLOOKUP formula to bring in the list number of the account code. This means you can change the date for the report and/or the account codes used (if the account codes are static, you don’t need the VLOOKUP formula - just a number).

The INDEX formula is then simple: it should read =INDEX(Range, Row Number, Column Number)

The Range is obviously your table containing your budgets (excluding headers); the Row Number refers to the VLOOKUP formula beside each of you account codes; the Column Number refers to the VLOOKUP formula at the top of your report by the month-end date. As long as you “fix” the right elements of your INDEX formula usinf the "$" sign, you can then copy and paste it wherever you like. Just hide the rows / columns containing the “reference” VLOOKUPs for the sake of presentation.

All this, and you don’t need to learn Access…

Thanks (0)
avatar
By AnonymousUser
23rd Jan 2001 09:19

Try this
The VlookUp function has the following arguments What to look for (I use nominal codes on the accounts page in a hidden column which match the nominal codes in the budget sheet),Where to look (I use the Name function to name the Budget data area),which column is the answer in (I put a number in the accounts columns for each month which represents the column number for each month in the Budget Data area),0 for exact match.

Use the Name function to name the Vlookup formula making sure the cursor is in the first cell on the accounts page which has the look up formula. Then just copy the name throughout the accounts sheet and the budget for each month will appear.

A refinement to the formula would be to add if(iserror(vlookup(etc)),0 or "",vlookup(etc)).

Hope this helps!

Thanks (0)
avatar
By cbales
23rd Jan 2001 13:52

Indeed not a lot to go on, but ......
William
Have you considered using the "choose" function instead. It's very useful for pulling in data from other source sheets just by the entry of a single reference. Choose allows you up to 29 alternatives and its not difficult to set up.

Thanks (0)
Simon Hurst
By Simon Hurst
22nd Jan 2001 16:18

Use the lookup wizard?
Not a lot to go on, but have you tried installing the lookup wizard add-in and using that?

Regards

Simon

Thanks (0)
avatar
By neileg
22nd Jan 2001 16:42

Yes indeed, not a lot to go on
You can use variables in the offset bits of VLOOKUP, so this could adjust to match different periods, so you would, say, input the month number once in the sheet, and base the formulae on this.

It would be even easier in Access...

Thanks (0)