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.
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!
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.
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
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...