Is there an Excel formula (or combination thereof) that will return all of (only) the first years that my data crosses the next multiple of the threshold?
Also, can I then do a SUMIF function on the premis that (if A8 contains "YR4"…)?


Also posted at:

http://answers.microsoft.com/en-us/o...=1470850338622

http://www.excelforum.com/editpost.p...postid=4453160

Attached Files

Excel Formula Question.xlsx (11.2 KB, 8 views)

Hi
please post a sample sheet, it's easier to work with than a picture. Thanks

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Please do not cross post without adding links to your other posts. Read this
to understand what it's all about. Thank you

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Corrected. My apologies.

Thank you for helping

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

I don't know if this can be of any use but:

=INDEX(C3:L3,N(IF(1,MATCH({1,2,3,4,5};INT(C4:L4/600);0)))) (array formula)

will return an array with your wanted result. The cell will only show YR2, but try to select the cell with the formula, then press F2 and afterwards press F9.

Hmm...I figured it would look something like that (I read INDEX/MATCH is better than VLOOKUP), but I'm getting an error message due to the array. Pressing F2 and F9 or CTRL+Shift+Enter doesn't help. I'm not very familiar with arrays, so I assume I'm just doing something wrong. Any ideas?

See the attached.

Attached Files

YR1.xlsx (12.3 KB, 15 views)

Thanks everyone, I appreciate all the help! Unfortunately, I've been directed to abandon this project and go in a completely different direction.