It's probably easy but I don't know how....

I attach the file so I can explain myself better.
In column "F" I need a function that, when column "E" is 1, check the MIN of the previous 10 value of column "A" and than put the corresponding value of column "C"

So, let's say, E12=1--->MIN(A12:A3)=A11 so in F12 I want C11
than in F24 I want C15 ecc...ecc...

I hope you can understand what I mean.
I'm getting addicted to this forum, it's really a lot of help for newbe like me. Thanks

in F1 and copy down

=IF(E12=1,MIN(A3:A12),"")

Originally Posted by AlanY

in F1 and copy down

=IF(E12=1,MIN(A3:A12),"")

Nope, because I don't need the MIN of column A but the corresponding value of column C

Originally Posted by MarvinP

Hi Adrin,

Alan meant to say in F12 put his formula. Then you can pull the formula down the entire column. See the attached. This formula works because of "relative reference" formulas. That means a formula's scope, without dollar signs, is relative to the cell the formula is in. Read http://www.cpearson.com/excel/relative.aspx for more.

After seeing you want the value in C (above) try this
=IFERROR(VLOOKUP(IF(E12=1,MIN(A3:A12),""),A3:C12,3,FALSE),"")

Hi Marvin, I don't know how to explain myself but the problem is that I need values of C not of A in column F. For example in F12 I need 509777073.

If you paste this into cell F12:
=VLOOKUP((IF(E13=1,MIN(A3:A12))),A3:C12,3,FALSE)

Should work, but returns #N/A if if cell in column E is not 1?

Hi Hardbody, thanks for the answer. Your formula work but it's not good for me. First I have problems with N/A and second I would need one that I can use when the reference value is not in the leftmost column. I mean, I can't always use VLOOKUP, I need to learn how to do it in a middle column.
At least you understand my problem and partially solve it. Thanks a lot

Originally Posted by MarvinP

I updated my formula and attached sheet. Does it work now?

Hi again,

The IfError() function came in 2007 and I see you have 2003 excel. So use this hint to not see those error messages.

http://answers.microsoft.com/en-us/o...0-6c93b533afe9

Marvin, thanks a lot. That work very good. Can you tell how can I do it without VLOOKUP, if I need to do the same in a column in the middle of the sheet. Really thanks.

I think you have to use INDEX and MATCH instead of VLOOKUP, but not familiar with them.

Originally Posted by AdrinBig

Nope, because I don't need the MIN of column A but the corresponding value of column C

ok, try this in F12 and copy down

=IF(E12=1,INDEX(C:C,MATCH(MIN(A3:A12),A:A,0)),"")