Sheets - Max value from lookup column

0
votes

Using Google Sheets, I have a reference sheet with names along the Header and 3 values below each. On the primary sheet, I want to have a name input in cell B8 and a formula in C12 to take that name and lookup in sheet 'Range' for the 3 values but return the highest value of the 3.

Here is the shared sheet: HighValueTest google sheet

Once I have that value (between 1 and 5) I want to highlight a column of cells representing the count of that value.

Any help greatly appreciated!

1
google-sheetsmaxlookup
Not sure about your question. Checked out the google sheet and simply wrapping the hlookup function with max seemed to work i.e. ArrayFormula(max(hlookup(B8,Range!B1:BD4,{2;3;4},false))) – KolaB

1 Answers

0
votes

Here's a formula to copy it down for that max values of your heroes:

=MAX(INDEX(Range!$B$2:$4, 0, MATCH(B8, Range!$B$1:$1, 0)))

enter image description here

And here is the formula for conditional formatting for that indicator rows (apply to the range B2:B6):

=MAX($C$21:$C$23) >= (5 - ROW() + ROW($B$2))

$C$21:$C$23 here is just the range where we've put our max values before.

enter image description here