# Variable Lookup based on another formula

#### gaftalik

##### Well-known Member
Hi everybody ,

I need to return the months names of V4 (the last 2 months in this case ) of index J7:U7, is that possible ?

The answer should be November & December.

Many thanks .
question abt average with index.xls
JKLMNOPQRSTUVW
4Averagelast2months
55
612
7OctoberNovemberDecember
8UTTOTALUTTOTAL
931811543349%33911245150%21124052269%487
1029814444251%31613344950%12211024031%345
11875900762
Sheet1

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### IML

##### MrExcel MVP
Not sure if this is what your after, but you could try
="Average of "&TEXT(EDATE(\$R\$7&0,-V1+1),"mmm-")&TEXT(\$R\$7&0,"mmm")

this requires the tool pak be activated and assumes R7 is text as opposed to a date

#### just_jon

##### Legend
Only going to be these 3 cells? Or is 4-12 possible.

##### MrExcel MVP
You have month names in merged cells. Assuming that each month name is actually in the cell above the the cell of U-field...

In W5 enter & copy down:

=IF(ROW()-ROW(\$W\$5)+1<=\$V\$4,INDEX(\$J\$7:\$U\$7,(\$V\$6-(\$V\$4-(COUNTA(\$W\$4:W4)-1))*4)+1),"")

You need to test this though with V4 set to different values.

#### gaftalik

##### Well-known Member
Champ !, i tried it , it worked as it is but it returned only November instead of November & December .

Shall i guess that since it shall return the last 2 months and November is the first that means what shall come after is definitely december or a tuning in the formula could be done ?

Dear IML ur formula returned an error!?
Dear Just_Jon , yes it could be more than 3 cells

Thank you all for your concern .

#### just_jon

##### Legend
I think, in order for the problem to have an optimum [ == Aladin ] solution, you'd have to let us know what the largest possible range of interest might be...

#### gaftalik

##### Well-known Member
IML said:
Not sure if this is what your after, but you could try
="Average of "&TEXT(EDATE(\$R\$7&0,-V1+1),"mmm-")&TEXT(\$R\$7&0,"mmm")

this requires the tool pak be activated and assumes R7 is text as opposed to a date

Sorry , when i put the dates as true dates, the formula returned
"Average of feb-mar" where as it considere the 2nd month and the 3rd month of the year not from the index i have (J7:U7)
question abt average with index.xls
JKLMNOPQRSTUVWX
4Averagelast2months
5
6
7oct.03nov.03dec.03
8UTTOTALUTTOTAL
931811543349%33911245150%21124052269%469Averageoffeb-mar
1029814444251%31613344950%12211024031%377
11875900762
Sheet2

##### MrExcel MVP
gaftalik said:
...i tried it , it worked as it is but it returned only November instead of November & December .

Shall i guess that since it shall return the last 2 months and November is the first that means what shall come after is definitely december or a tuning in the formula could be done ?...

Month names should be in the first cell of the merged cells in row 7...
aaReturnNameLastNmonths gaftalik.xls
JKLMNOPQRSTUVW
4Averagelast2months
5November
6December
7OctoberNovemberDecember
8UTTOTALUTTOTAL
9318115339112211240522
10298144316133122110240
11
Sheet1

the formula in W5, which is copied down, is:

=IF(ROW()-ROW(\$W\$5)+1<=\$V\$4,INDEX(\$J\$7:\$U\$7,(\$V\$6-(\$V\$4-(COUNTA(\$W\$4:W4)-1))*4)+1),"")

The ROW(\$W\$5) anchors the formula to the first formula cell which is W5.

#### gaftalik

##### Well-known Member
Once i turned the dates to true dates such in the table,please check , since i need also to retrieve the nbr of days of those months ...i have to apply the formula of Mr Aladdin.
question abt average with index.xls
JKLMNOPQRSTUVWX
4Averagelast2months
55nov.03
612dec.03
7oct.03nov.03dec.03
8UTTOTALUTTOTAL
931811543349%33911245150%21124052269%487Averageoffeb-mar
1029814444251%31613344950%12211024031%345
11875900762
Sheet2

Thank you both for the answers and for your attention .

Replies
2
Views
399
Replies
0
Views
204
Replies
9
Views
128
Replies
3
Views
583
Replies
4
Views
661

1,181,896
Messages
5,932,679
Members
436,850
Latest member
Jasperlee93

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back