0

I have a question about formulas, it´s not an easy one, I hope someone can solve it.

Table looks like this

Idx Val Rslt
1   1   3
1   2   3
1   3   3
2   11  13
2   12  13
2   13  13

Named range NR encapsulates columns Idx and Val (without headers).

I would like to have a formula for Rslt column cells that :

a) recognizes rows with same Idx value

b) returns max Val from that set of rows

I hope I was clear, if not - feel free to ask.

Thanks

Improve this question

1 Answer 1

Reset to default
2

=MAX(IF($A$2:$A$7=A2,$B$2:$B$7))

Type this in to C2 and then press ctrl+shift+enter, it will then fill the formula down.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Ok, you are assuming that first row is empty. Unfortunately your formula gives 13 instead of 3 as first result. I did it other way, it's maybe complicated but it works here, formula is : =MAX(INDEX((INDEX(A2:B7,0, 1)=A2)*(INDEX(A2:B7,0, 2)),0)) --- Thanks!
@Milan It gives 13 if you don't create the aray by pressing ctrl+shift+enter when you get some time do some research on ctrl+shift+enter it may save you some time on future issues instead of using slightly more complexed index formula's. Also don't forget to use absolute values ($) so that you can easily copy your formula down. Good luck!
I though CSE will "fill the formula down" was used to copy the formula in the cells below. Sorry for misunderstanding. I just tried it and it works perfect. And I am reading about CSE formulas, great stuff, I will surely use them in the future. Thanks a million !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.