Using Index & Match Functions

**DanSherwood** · 01-31-2008, 12:56 PM

Hi,

I have a problem, attached spreasheet lays it out with explanation based on this attachment below...

Basically in the section with the blue border i need to enter the date of a press advert (in yellow cell) which will output the day of the week in the green cell. Take row 3 as an example in the attached: So when i put in that an advert was in the Times i can add the date it went out and therefore what day it was on, in this case 6th Feb, Wednesday.

In the section with the black border in the attached i have a reference table that shows the % of calls that will result from an advert based on which day it was placed, for the following 11 days. For example in row 12 in this table, it shows that an ad placed on a sunday will generate 0% of its calls on that 1st day, 50% on the 2nd day, 20% on the 3rd day and so on..

What i need to happen is that in the red section of the attached, the % of calls generated from the reference table will be automatically inserted based on the date of the advert i have entered in the yellow cells..

So if i change the date of an advert it will automatically populate the red bordered section for that row, with the relevant % of calls per day, based on it knowing what day 1 is (from the date i have entered) and therefore matches this against the reference table and then knows to move onto day 2 calls the following day etc.

It should make sense if you read this whilst looking at the attachment!

I believe i need some index/match formula wizardry but i am not advanced enought to do that and i do not want the solution in VBA if possible.

Thanks in advance for any help.

**DanSherwood** · 02-01-2008, 07:24 AM

Surely i've not stumped the experts???

**martindwilson** · 02-01-2008, 08:32 AM

to return day of week you could use in col c

Please Login or Register  to view this content.

this probably can be shortened!

**daddylonglegs** · 02-01-2008, 08:46 AM

You just want the percentages in the relevant days? Try this formula in J3 copied across and down

=IF(OR(J$2<$B3,J$2-$B3>10),"",INDEX($C$12:$M$18,MATCH( $C3,$B$12:$B$18,0),MATCH("Day"&J$2-$B3+1,$C$11:$M$11,0)))

Note: for your example, because column J date is before 6th Feb you'll just get a blank in J3 but copy the formula across (or change the date in J2) and you should see the percentages required

Note: C3 can just be

=TEXT(B3,"ddd")

**DanSherwood** · 02-01-2008, 09:36 AM

you are a legend, thankyou very much.

**tryingtoexcel** · 03-22-2008, 02:14 AM

I input data every day for my sales people. Today is Friday, March 21, 2008, and I AM looking for a way to make cell L9 to read the next Sunday. My reports are based on Monday to Sunday. i.e, if today is Friday, how can I make cell L9 display 3-23-2008. or if today is monday the same way. AND if today is-in-fact Sunday to leave it alone.

AM I asking for too much

thx
(trying t excel)