1

Link to Google Sheet Sample

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)))
jpf
  • 1,447
  • 12
  • 22
Lisa
  • 897
  • 9
  • 27

2 Answers2

2

Your issue is that your two tabs are of different size AND you have open ranges.
Your Calc tab is 1002 rows, while Data is 2001 rows.

You have 2 alternatives

Solution 1
Add just one more row in your Data tab or delete one from your Calc tab.

Solution 2
Use closed ranges as in 

=ARRAYFORMULA(IF(ISBLANK($A3:$A888);;$B3:$B888&":"&MAX((Data!$B3:$B888=$B3:$B888)*Data!$C3:$C888)&":"&MAX((Data!$B3:$B888=$B3:$B888)*Data!$D3:$D888)))

In either case, make sure your ranges have the same amount of rows.

marikamitsos
  • 10,264
  • 20
  • 26
  • Will the number of rows be different again when I delete one row later on (e.g. deleting one person's data in 'Data')? Because that's a possibility within the Data range. If so, is there some way to prevent the formula going haywire when that happens? – Lisa May 28 '20 at 20:21
  • You can either add one or delete one. Just make sure your open ranges have the same amount of rows. – marikamitsos May 28 '20 at 20:23
  • It's updated! Btw, I'm just afraid that -when the sheet's is in use later on- a row gets deleted in 'Data' somehow and the formula breaks because of the different number of rows. So I'm wondering if that's preventable by editing the formula in some way. – Lisa May 28 '20 at 20:26
  • 1
    Don't use open ranges. Just say `Data!$B3:$B888=$B3` and change the rest accordingly – marikamitsos May 28 '20 at 20:33
  • While the Arrayformula works now, the code is not fixed yet, it only grabs the value from the first row. How could I fix that? – Lisa May 28 '20 at 20:44
0

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")
Lisa
  • 897
  • 9
  • 27