At a loose end with formulas.....Help Please

[magneyhead]

Hi,

This is my first post and I despartely need help: I am using excel 2003.

My problem...

I have dates in cells N10, AB10, AP10, BD10 etc....

What i need excel to do is:
Find which cell has a value of "31/05/11" (N10, AB10, AP10, BD10)
and return the value in the cell one down and one to the left (M11, AA11, AO11, BC11)

This is probably very simple but i am just stuck.

Thanks

Charmagne

 

[Andrew Poulsom]

Welcome to MrExcel.

How about?

=INDEX(M11:BC11,MATCH(DATE(2011,5,31),N10:BD10,FALSE))

 

[Aladin Akyurek]

Hi,

This is my first post and I despartely need help: I am using excel 2003.

My problem...

I have dates in cells N10, AB10, AP10, BD10 etc....

What i need excel to do is:
Find which cell has a value of "31/05/11" (N10, AB10, AP10, BD10)
and return the value in the cell one down and one to the left (M11, AA11, AO11, BC11)

This is probably very simple but i am just stuck.

Thanks

Charmagne

N2: 31-May-11

N3, control+shift+enter, not just enter:

=INDEX(M11:BC11,MATCH(1,IF(MOD(COLUMN(N10:BD10)-COLUMN(N10),14)=0,IF(N10:BD10=N2,1)),0))

 

[magneyhead]

Thank you for your reply.....I am getting a #NA error for both forumlas? Any ideas?

 

[Andrew Poulsom]

That means that the date can't be found. Make sure that it's a serial date not a text entry that looks like a date - you can use the ISNUMBER function to check.

 

[magneyhead]

Thank you so much - this now works

Wooo exiciting

 

[magneyhead]

I have one last question I wondered if you could help with please: In the cell where i am using the formula how do i get #n/a to appear as 0's?
Thanks
Charmagne

 

[Andrew Poulsom]

Try:

=IF(COUNTIF(N10:BD10,DATE(2011,5,31)),INDEX(M11:BC11,MATCH(DATE(2011,5,31),N10:BD10,FALSE)),0)