In column E on sheet 'Calc', I want to check if there's a person in sheet 'Data' who has the same A value, but a higher value on B and C. Eventually I want to Return 'No' if not, return 'yes' if yes. If there's a person with the same A value who has a higher value on B but not on C (or vice versa) return 'maybe' with the name of the person(s). Autopopulate E with the returned value.
The values in columns B, C and D in 'Calc' are dynamic and will change based on other calculations. The B, C and D values in 'Data' are static. The length of both columns can differ.
Some examples:
Name 1 in sheet 'Calc' has the values of A89, B70, C70.
Person A in sheet 'Data' has the values of A89, B71, C75. No-one else has A89 and higher values on both B or C.
Return 'Yes'.
Name 1 in sheet 'Calc' has the values of A89, B70, C70.
Person A in sheet 'Data' has the values of A89, B60, C55. No-one else has A89 and higher values on both B or C.
Return 'No'.
Name 1 in sheet 'Calc' has the values of A89, B70, C70.
Person A in sheet 'Data' has the values of A89, B71, C55. No-one else has A89 and higher values on both B or C.
Return 'Maybe: Person A'.
Name 1 in sheet 'Calc' has the values of A89, B70, C70.
Person A in sheet 'Data' has the values of A89, B71, C55. Person B in sheet 'Data' has the values of A89, B55, C71. Person A has the highest value on B compared to the people in sheet 'Data' with A89 but a C lower than that of Name 1 in sheet 'Calc'. Person B has the highest value on C compared to the people in sheet 'Data' with A89 but a B lower than the of Name 1 in sheet 'Calc'.
Return 'Maybe: Person A, Person B'.
I've tried to make a start by just getting the highest values in sheet 'Calc' by combining =ARRAYFORMULA with MAX, but I get either a 'different number' array error or everything is just compared to B3.
Error:
=ARRAYFORMULA(IF(ISBLANK($A3:$A);;$B3&":"&MAX((Data!$B3:$B=$B3)*Data!$C3:$C)&":"&MAX((Data!$B3:$B=$B3)*Data!$D3:$D)))
Only calculated with B3:
=ARRAYFORMULA(IF(ISBLANK($A3:$A);;$B3&":"&MAX((Data!$B3:$B=$B3)*Data!$C3:$C)&":"&MAX((Data!$B3:$B=$B3)*Data!$D3:$D)))
Edit: I've removed the open-ended ranges which at least removed the error I got, but it still doesn't return the desired numbers (it returns either zero or the highest numbers belonging to the first entry, B3). I've updated the sheet accordingly.
=ARRAYFORMULA(IF(ISBLANK($A3:$A999);;$B$3:$B$999&":"&MAX((Data!$B3:$B999=$B$3:$B$999)*Data!$C3:$C999)&":"&MAX((Data!$B3:$B999=$B$3:$B$999)*Data!$D3:$D999)))
I've not found a way to autopopulate it yet, but this solution works for me because I can just copy it to a lot of cells by combining an IF-statement with a ISBLANK() formula.
Basically I've used this, wrapped in a couple of if-statements, changed the returned data according to my needs, and built the code up from there. It returns the desired cell-value from the person with the highest B-value and who's A-value corresponds with the one on the CALC sheet.
INDEX(
Data!$C:$C
;
MATCH(
MAXIFS(
Data!$C$3:$C$100
;
Data!$B$3:$B$100
;
$B3
)
;
Data!$C:$C
;
0
)
)
The code in column M in the sample-sheet is the code that I've started using. It's not pretty (at all) and could probably be a lot more efficient, but it works and this already took a lot of googling, tries and patience, so I'm a bit at the limit of my google-formula knowledge, haha.
This is the formula I've implemented in this sheet for conditional formatting, should someone need something like it. It picks the maximum value of a column based on a criteria in another:
=$E3=MAXIFS($E$3:$E$100;$M$3:$M$100;"Yes")