Three Way Lookup, with a Hard Twist. Please Help!

Disturbed

New Member
Joined
Jan 7, 2014
Messages
7
KnownAB
Look up tableXYZ
A
A1
2.5
AB211
AC0.250
BA211
BB.541
BC212

<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
Joined
Nov 6, 2008
Messages
1,484
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?

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="color: #333333;background-color: #FAFAFA;;">Look up table</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;"></td><td style="color: #333333;background-color: #FAFAFA;;">X</td><td style="color: #333333;background-color: #FAFAFA;;">Y</td><td style="color: #333333;background-color: #FAFAFA;;">Z</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="color: #333333;background-color: #FAFAFA;;">A</td><td style="color: #333333;background-color: #FAFAFA;;">A</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0.5</td><td style="text-align: right;;"></td><td style=";">Y</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="color: #333333;background-color: #FAFAFA;;">A</td><td style="color: #333333;background-color: #FAFAFA;;">B</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1</td><td style="text-align: right;;"></td><td style=";">X</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="color: #333333;background-color: #FAFAFA;;">A</td><td style="color: #333333;background-color: #FAFAFA;;">C</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0.25</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0</td><td style="text-align: right;;"></td><td style=";">Y</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="color: #333333;background-color: #FAFAFA;;">B</td><td style="color: #333333;background-color: #FAFAFA;;">A</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1</td><td style="text-align: right;;"></td><td style=";">X</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="color: #333333;background-color: #FAFAFA;;">B</td><td style="color: #333333;background-color: #FAFAFA;;">B</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">0.5</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">4</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1</td><td style="text-align: right;;"></td><td style=";">Y</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="color: #333333;background-color: #FAFAFA;;">B</td><td style="color: #333333;background-color: #FAFAFA;;">C</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">1</td><td style="text-align: right;color: #333333;background-color: #FAFAFA;;">2</td><td style="text-align: right;;"></td><td style=";">X</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G2</th><td style="text-align:left">=IF(<font color="Blue">C2=MAX(<font color="Red">C2:E2</font>),$C$1,IF(<font color="Red">D2=MAX(<font color="Green">C2:E2</font>),$D$1,IF(<font color="Green">E2=MAX(<font color="Purple">C2:E2</font>),$E$1,""</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G3</th><td style="text-align:left">=IF(<font color="Blue">C3=MAX(<font color="Red">C3:E3</font>),$C$1,IF(<font color="Red">D3=MAX(<font color="Green">C3:E3</font>),$D$1,IF(<font color="Green">E3=MAX(<font color="Purple">C3:E3</font>),$E$1,""</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G4</th><td style="text-align:left">=IF(<font color="Blue">C4=MAX(<font color="Red">C4:E4</font>),$C$1,IF(<font color="Red">D4=MAX(<font color="Green">C4:E4</font>),$D$1,IF(<font color="Green">E4=MAX(<font color="Purple">C4:E4</font>),$E$1,""</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G5</th><td style="text-align:left">=IF(<font color="Blue">C5=MAX(<font color="Red">C5:E5</font>),$C$1,IF(<font color="Red">D5=MAX(<font color="Green">C5:E5</font>),$D$1,IF(<font color="Green">E5=MAX(<font color="Purple">C5:E5</font>),$E$1,""</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G6</th><td style="text-align:left">=IF(<font color="Blue">C6=MAX(<font color="Red">C6:E6</font>),$C$1,IF(<font color="Red">D6=MAX(<font color="Green">C6:E6</font>),$D$1,IF(<font color="Green">E6=MAX(<font color="Purple">C6:E6</font>),$E$1,""</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">G7</th><td style="text-align:left">=IF(<font color="Blue">C7=MAX(<font color="Red">C7:E7</font>),$C$1,IF(<font color="Red">D7=MAX(<font color="Green">C7:E7</font>),$D$1,IF(<font color="Green">E7=MAX(<font color="Purple">C7:E7</font>),$E$1,""</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

dispelthemyth

Well-known Member
Joined
Mar 16, 2006
Messages
654
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

*ABCDE
1KnownABLookup return2
2
3Look up tableXYZ
4AA120.5
5AB211
6AC00.250
7BA211
8BB0.541
9BC212

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
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
Joined
Aug 13, 2011
Messages
2,932
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
13Result column headerX

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1

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

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

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

<thead>
</thead><tbody>
</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
Joined
Jan 7, 2014
Messages
7

ADVERTISEMENT

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
 

Disturbed

New Member
Joined
Jan 7, 2014
Messages
7

ADVERTISEMENT

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.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,203
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))),"")
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top