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

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• May 31, 2011

• #2

Welcome to MrExcel.

How about?

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

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• May 31, 2011

• #3

magneyhead said:

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

Click to expand...

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))

 

Upvote 0

M

magneyhead

New Member

Joined

May 31, 2011

Messages

7

• May 31, 2011

• #4

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

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• May 31, 2011

• #5

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.

 

Upvote 0

M

magneyhead

New Member

Joined

May 31, 2011

Messages

7

• May 31, 2011

• #6

Thank you so much - this now works

Wooo exiciting

 

Upvote 0

M

magneyhead

New Member

Joined

May 31, 2011

Messages

7

• May 31, 2011

• #7

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

 

Upvote 0

Andrew Poulsom

MrExcel MVP

Joined

Jul 21, 2002

Messages

73,092

• May 31, 2011

• #8

Try:

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

 

Upvote 0