Index / match functions

[Helppplease]

Is anybody able to assist me please. I've never posted in this forum before but i've found it very useful to see threads already posted.

Basically I want to bring back the result of "Mar-13 into cell A2. The result being the month that data on line 5 was less than 0.

A B C D E F G
2 #REF!
3
4 Sep-11 Dec-11 Mar-12 Jun-12 Sep-12 Dec-12 Mar-13
5 500,000 500,000 500,000 500,000 500,000 500,000 -800,000

I need to avoid changing the order of the info ie not using a hlookup.

I was trying =INDEX($A$3:$G$3,MATCH(A7,A4:G4,FALSE),1) where A7 was -800,000 to no avail.

Any assistance would be appreciated.

Tx

helpppplease

[Andrew-R]

If you know the value you're looking for (i.e. it's in cell A7) then your formula is nearly right:

=INDEX($A$3:$G$3,1,MATCH(A7,A5:G5,0))

[Helppplease]

Thank you Andrew-R - that now works. Can i take it a step further to bring back the month the first time A5:G5 is less than 0?

[Marcol]

You have your column reference where you should have the row reference.
The syntax is ...
INDEX(array,row,column)

See this workbook, select your date from the drop-down in A1

[Helppplease]

Thanks Marcol. I can see what you've done which is pretty nifty but i don't think i was clear in my post for what I was trying to achieve. i'm almost there with what Andrew-R posted.

[vlady]

good am..
in your sample you used row A4 to G4

but used the index A3 to G3
with Andrews formula

=INDEX(A4:G4,1,MATCH(A7,A5:G5,0))

then format the cell with date

[Marcol]

... Can i take it a step further to bring back the month the first time A5:G5 is less than 0?

Try this array formula

=INDEX(A3:G3,1,MATCH("-*",TEXT(A4:G4,"0"),0))

Confirm with Ctrl+Shift+Enter not just Enter

See A7 in this workbook, we seem to be talking about different rows.

[Helppplease]

Marcol - You are a superstar..... THANK YOU THANK YOU THANK YOU.