I have a table where the header row is three merged cells and there are multiple columns below corresponding to that title. What I need is a formula that looks up (presumably using HLOOKUP) the name, before then finding the maximum value on any row below.
<tbody>
</tbody>
<tbody>
</tbody>
So a search for Person B looking for the maximum value on row 3 would return the value 12.
OR
a search for Person A, finding the max value for them on row 5 would return the value 21.
Any help is appreciated.
Person A | Person B |
<tbody>
</tbody>
1 | 2 | 3 | 4 | 5 | 6 |
7 | 8 | 9 | 10 | 11 | 12 |
13 | 14 | 15 | 16 | 17 | 18 |
19 | 20 | 21 | 22 | 23 | 24 |
<tbody>
</tbody>
So a search for Person B looking for the maximum value on row 3 would return the value 12.
OR
a search for Person A, finding the max value for them on row 5 would return the value 21.
Any help is appreciated.