#### Disturbed

##### New Member
 Known A B Look up table X Y Z A A 1 2 .5 A B 2 1 1 A C 0 .25 0 B A 2 1 1 B B .5 4 1 B C 2 1 2

<tbody>
</tbody>

That's the basic layout of what I have. Now what I am looking for is a Formula that I can use the 2 known A and B to find the A | B row.
Than off of that row to find the maximum within the row.
Finally to give me the name of the column that the max number is in.

Example- using A | B the max number would be 2, than column is X

If someone could figure out how to do that in a 1 cell formula or even 2-3 step formulas to come out with the right answer I would be grateful.

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### shyy

##### Well-known Member
Not sure I am reading this correctly,

You don't need to worry about column A or B, you just need to pick up the largest values from Column X, Y and Z or did you want to search columns that have A & B only?

#### dispelthemyth

##### Well-known Member
Is X y and Z your columns, i dont get what you want to do with them

Can you clarify as from reading it, i only see you mentioning the Max value for A & B, not the X

Sheet1

 * A B C D E 1 Known A B Lookup return 2 2 3 Look up table X Y Z 4 A A 1 2 0.5 5 A B 2 1 1 6 A C 0 0.25 0 7 B A 2 1 1 8 B B 0.5 4 1 9 B C 2 1 2

<tbody>
</tbody>

 Cell Formula E1 =MAX(OFFSET(INDIRECT("C"&SUMPRODUCT(--((\$A\$4:\$A\$9=\$B\$1)*(\$B\$4:\$B\$9=\$C\$1)*ROW(\$A\$4:\$A\$9)))),0,0,1,3))

<tbody>
</tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Last edited:

#### circledchicken

##### Well-known Member
Hi,

My understanding is that you also want the column header value as a result.
Perhaps like this:

Excel 2013
ABCDE
1KnownAB
2
3Look up tableXYZ
4AA120.5
5AB211
6AC00.250
7BA211
8BB0.541
9BC212
10
11Result row2
12Result max value2

</tbody>
Sheet1

Worksheet Formulas
CellFormula
B12=MAX(INDEX(C4:E9, B11, 0))
B13=INDEX(C3:E3, MATCH(B12, INDEX(C4:E9, B11, 0), 0))

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
B11{=MATCH(C1, IF(A4:A9 = B1, B4:B9), 0)}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

#### Disturbed

##### New Member

Thank you all for your help, the last Post here worked perfectly for what I was doing.

Thank you all again, It was a big help.
Disturbed

##### MrExcel MVP
Can A and B occur together multiple times? Or is it just once or never?

#### Disturbed

##### New Member

A | B
B | A
^ Those are the only Two times that the values would occur together.
and the way the data is to get the values A and B are flopped all around so both A | B and B | A are needed.

On the side there are 18 different columns and 323 different rows so there are a lot more than the small example of it.

##### MrExcel MVP
A | B
B | A
^ Those are the only Two times that the values would occur together.
and the way the data is to get the values A and B are flopped all around so both A | B and B | A are needed.

On the side there are 18 different columns and 323 different rows so there are a lot more than the small example of it.

You have A as look up value for column A and B as look up value for column B. What I was asking is whether A and B can occur than once in the data:

A,B,3,7,7
A,B,4,2,1

#### Disturbed

##### New Member
A and B do not occur more than once.

##### MrExcel MVP
A and B do not occur more than once.

Ok, thanks. What follows is a different approach...

 Known A B Result(s) X X Y Z Z A A 1 2 0.5 A B 7 1 7 A C 0 0.25 0 B A 2 1 1 B B 0.5 4 1 B C 2 1 2

<TBODY>
</TBODY>

H2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``````=IFERROR(INDEX(\$C\$3:\$E\$3,SMALL(IF(\$C\$4:\$E\$9=MAX(INDEX(\$C\$4:\$E\$9,
MATCH(1,IF(\$A\$4:\$A\$9=\$B\$1,IF(\$B\$4:\$B\$9=\$C\$1,1)),0),0)),
COLUMN(\$C\$3:\$E\$3)-COLUMN(\$C\$3)+1),ROWS(\$H\$2:H2))),"")
``````

Replies
4
Views
205
Replies
1
Views
194
Replies
16
Views
102
Replies
10
Views
312
Replies
10
Views
87

1,130,310
Messages
5,641,450
Members
417,210
Latest member
rins

### 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