Variable Lookup based on another formula

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
512
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
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

IML

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Feb 6, 2003
Messages
512

ADVERTISEMENT

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
Joined
Sep 3, 2002
Messages
10,473
I think, in order for the problem to have an optimum [ == Aladin :LOL: ] solution, you'd have to let us know what the largest possible range of interest might be...
 

gaftalik

Well-known Member
Joined
Feb 6, 2003
Messages
512

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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
Joined
Feb 6, 2003
Messages
512
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 .
 

Watch MrExcel Video

Forum statistics

Threads
1,122,721
Messages
5,597,752
Members
414,171
Latest member
12Rev79

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?

Disable AdBlock

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
Top