Best formula to use

[excellrookie]

I am having a complete brain fart at work and cant think of the right formula to use.

Any help would be appreciated. Here is what i have.

Row 1 are dates, increasing by one month for every cell. for example
a2= jan 2009
b2= feb 2009
c2= mar 2009


Row 2 is a % amount corresponding to the month. For example:
A3= 34%
B3= 21%
C3= 13%

Row 3 is a value. For example:
A4= $10
B4= $25
C4= $30





So, I am completely spacing on a formula that does the following:

When i type in the date 1/1/2010 into cell a1, the formula looks up the right date in row 2, and returns me the value from row 3.

and

Another formula that does exactly the same thing but returns the value in Row 3


Thanks for your help. Coffee machine broke today so I have been dead on arrival at work.

 

[T. Valko]

Row 1 are dates, increasing by one month for every cell. for example
a2= jan 2009
b2= feb 2009
c2= mar 2009

When i type in the date 1/1/2010 into cell a1, the formula looks up the right date in row 2, and returns me the value from row 3.

Are the dates in row 2 the 1st of the month dates? For example, is the date in A2 1/1/2009?

When you enter a date in cell A1 will there always be an exact date match on row 2?

 

[excellrookie]

Are the dates in row 2 the 1st of the month dates? For example, is the date in A2 1/1/2009?

When you enter a date in cell A1 will there always be an exact date match row 2?

The dates are set to 1/1/2009, but i would ultimately like to type in just 2010 in A1 and have excel find that 2010 begins at cell D2...

 

[T. Valko]

The dates are set to 1/1/2009, but i would ultimately like to type in just 2010 in A1 and have excel find that 2010 begins at cell D2...

But if you have monthly data how can you tell what month should be returned when you're only entering the year number?

 

[Aladin Akyurek]

I am having a complete brain fart at work and cant think of the right formula to use.

Any help would be appreciated. Here is what i have.

Row 1 are dates, increasing by one month for every cell. for example
a2= jan 2009
b2= feb 2009
c2= mar 2009


Row 2 is a % amount corresponding to the month. For example:
A3= 34%
B3= 21%
C3= 13%

Row 3 is a value. For example:
A4= $10
B4= $25
C4= $30





So, I am completely spacing on a formula that does the following:

When i type in the date 1/1/2010 into cell a1, the formula looks up the right date in row 2, and returns me the value from row 3.

and

Another formula that does exactly the same thing but returns the value in Row 3


Thanks for your help. Coffee machine broke today so I have been dead on arrival at work.

If A2:C2 sorted in ascending order and consists of the first day dates of mont/year pairs...

=LOOKUP(A1,A2:C2,A3:C3)

=LOOKUP(A1,A2:C2,A4:C4)

Or equivalently...

=INDEX(A3:C3,MATCH(A1,A2:C2,1))

=INDEX(A4:C4,MATCH(A1,A2:C2,1))

If A1 must exactly match a date value in A2:C2, invoke:

=INDEX(A3:C3,MATCH(A1,A2:C2,0))

=INDEX(A4:C4,MATCH(A1,A2:C2,0))

 

[excellrookie]

Thanks a bunch, I will test this out and let you know how it goes

@Valko, my monthly data is in this format 1/1/2009 - 1/1/2011. So there is a cell for every month, for 2009, 2010 and 2011

 

[T. Valko]

Thanks a bunch, I will test this out and let you know how it goes

@Valko, my monthly data is in this format 1/1/2009 - 1/1/2011. So there is a cell for every month, for 2009, 2010 and 2011

OK, see if this is what you had in mind...

Let's assume this is your data:

Book1
	A	B	C	D
1	2/1/2009
2		1/1/2009	2/1/2009	3/1/2009
3		34%	21%	13%
4		10	25	30
Sheet1

Enter this formula in A10:

=INDEX(B3:D3,MATCH(A$1,B$2:D$2,0))

Copy down to A11.

Format cell A10 as Percentage.