# Using HLOOKUP with merged cells

#### ATSJ

##### Board Regular
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.

 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.

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### FDibbins

##### Well-known Member
If your sample really is that simple...
 G​ H​ I​ J​ K​ L​ 1​ Person A​ ​ ​ Person B​ ​ ​ 2​ 1 2 3 4 5 6 3​ 7 8 9 10 11 12 4​ 13 14 15 16 17 18 5​ 19 20 21 22 23 24 6​ 7​ Person b Row 3 Max 12​
L7=MAX(OFFSET(\$G\$1,\$J\$7-1,MATCH(\$G\$7&" "&\$H\$7,\$G\$1:\$L\$1,0)-1,1,3))

Replies
4
Views
122
Replies
2
Views
179
Replies
2
Views
175
Replies
2
Views
128
Replies
5
Views
205

1,190,795
Messages
5,982,963
Members
439,808
Latest member
agutosay

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back