1

Can someone help me with the formula here? I have used something like this before ={MAX(IF(...} but it doesn't really fit what I'm doing now.

=MAX(IF(Sheet1!$A$a:$A$1500=Sheet3!D19,Sheet1!$G$1:$G$1500))

I need to identify that I have duplicates in column A, then look at column B and find the highest value for e.g. cat, then match this highest value with the highest in column C (as you can see "cat" has two 140 figures but in C one of them =56, thus I need "OK" next to it). I would be greatly obliged for your help. 

Col A B C Formula? cat 140 56 OK cat 140 54 x cat 87 41 x cat 91 13 x cat 100 11 x dog 230 12 x dog 230 55 OK dog 230 45 x mouse 111 12 x mouse 123 43 x mouse 145 55 OK mouse 103 54 x

enter image description here

Improve this question
0

1 Answer 1

Reset to default
2

Try this in C2 and fill down.

=IF(AND(B2=AGGREGATE(14, 7, B:B/(A$1:A$13=A2), 1), C2=AGGREGATE(14, 7, C:C/((A$1:A$13=A2)*(B$1:B$13=AGGREGATE(14, 7, B:B/(A$1:A$13=A2), 1))), 1)), "OK", "x")

enter image description here

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

2 Comments

Brilliant ! Thank you so much :)
Some of that formula could be cut down and made simpler by substituting the newer MAXIFS for the AGGREGATE functions but I am still posting to a de facto standard of xl2010 for the benefit of future viewers of this post and response.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.