I have a list of product sales values by month that go back a few years and I
wish to use a formula to workout the first month in which there was a sale
for each product. Is there a formula that can give a reference for the first
value (<0) so that I can lookup the relative position for the month.

Eg
Month 1 £0
Month 2 £0
Month 3 £0
Month 4 £500
Month 5 £0
Month 5 £450

Therefore 1st month is month 4.
I have tried match and offset but cannot get anything to work.

Maybe

=INDEX(A2:A10,MATCH(TRUE,B2:B100,0))

Commit with Ctrl+Shift+Enter.

Mike

"Mike@Gentech" wrote:

I have a list of product sales values by month that go back a few years and I
wish to use a formula to workout the first month in which there was a sale
for each product. Is there a formula that can give a reference for the first
value (<0) so that I can lookup the relative position for the month.

Eg
Month 1 £0
Month 2 £0
Month 3 £0
Month 4 £500
Month 5 £0
Month 5 £450

Therefore 1st month is month 4.
I have tried match and offset but cannot get anything to work.

=INDEX(A1:A20,MIN(IF(B1:B20<0,ROW(B1:B20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mike@Gentech" wrote in message
...
I have a list of product sales values by month that go back a few years and
I
wish to use a formula to workout the first month in which there was a sale
for each product. Is there a formula that can give a reference for the
first
value (<0) so that I can lookup the relative position for the month.

Eg
Month 1 £0
Month 2 £0
Month 3 £0
Month 4 £500
Month 5 £0
Month 5 £450

Therefore 1st month is month 4.
I have tried match and offset but cannot get anything to work.