Hi, I have an Excel 2007 spreadsheet which I'm attempting to make more dynamic, but struggling with the vlookup formula.
Firstly I have several named ranges: actual, budget, lastyear, forecast1, forecast2 showing data for weeks 1-52. I have set the vlookup formula so that regardless of what weeks data I want to look at, it will pull through the correct data, for example if I wanted to see actual sales for week 10, I just select "Week 10" on my summay page and the correct data will be shown.
However, this only works for actual data - as well as showing "Week No" on my summary page I also want to show "Data Required" so the user can choose any of the ranges I mentioned above (theory is that they select two sets of data, ie Actual and Budget and the two data sets are shown along with variances, etc).
I could set up a nested vlookup so that if data required = Actual, do this formula, if data required = Budget, do the next formula and so on, but I was hoping that there was a more succinct method, almost a lookup within a lookup but I've tried that and I can't get it to work
My idea was something like the following:
Then if "Budget" was selected as the data that was required, the following formula should determine which range I needed
This comes out with the answer = test2 which is the range that I actually need but if I use this within another vlookup formula, it doesn't recognise it as a range.
Can anyone help please?
edit: I probably should add that the reason these are under different named ranges as the data is on different sheets within the same workbook as they are in different formats as originate from different sources
Firstly I have several named ranges: actual, budget, lastyear, forecast1, forecast2 showing data for weeks 1-52. I have set the vlookup formula so that regardless of what weeks data I want to look at, it will pull through the correct data, for example if I wanted to see actual sales for week 10, I just select "Week 10" on my summay page and the correct data will be shown.
However, this only works for actual data - as well as showing "Week No" on my summary page I also want to show "Data Required" so the user can choose any of the ranges I mentioned above (theory is that they select two sets of data, ie Actual and Budget and the two data sets are shown along with variances, etc).
I could set up a nested vlookup so that if data required = Actual, do this formula, if data required = Budget, do the next formula and so on, but I was hoping that there was a more succinct method, almost a lookup within a lookup but I've tried that and I can't get it to work
My idea was something like the following:
Code:
Range: test1
Actual1 1
Actual2 2
Actual3 3
Range: test2
Budget1 6
Budget2 7
Budget3 8
Range: test3
Actual test1
Budget test2
Then if "Budget" was selected as the data that was required, the following formula should determine which range I needed
Code:
=vlookup("Budget",test3,2,false)
This comes out with the answer = test2 which is the range that I actually need but if I use this within another vlookup formula, it doesn't recognise it as a range.
Can anyone help please?
edit: I probably should add that the reason these are under different named ranges as the data is on different sheets within the same workbook as they are in different formats as originate from different sources
Last edited: