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

[Disturbed]

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.

 

[shyy]

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?

Excel 2010
	A	B	C	D	E	F	G
1	Look up table		X	Y	Z
2	A	A	1	2	0.5		Y
3	A	B	2	1	1		X
4	A	C	0	0.25	0		Y
5	B	A	2	1	1		X
6	B	B	0.5	4	1		Y
7	B	C	2	1	2		X
Sheet1

Cell Formulas
Range	Formula
G2	=IF(C2=MAX(C2:E2),$C$1,IF(D2=MAX(C2:E2),$D$1,IF(E2=MAX(C2:E2),$E$1,"")))
G3	=IF(C3=MAX(C3:E3),$C$1,IF(D3=MAX(C3:E3),$D$1,IF(E3=MAX(C3:E3),$E$1,"")))
G4	=IF(C4=MAX(C4:E4),$C$1,IF(D4=MAX(C4:E4),$D$1,IF(E4=MAX(C4:E4),$E$1,"")))
G5	=IF(C5=MAX(C5:E5),$C$1,IF(D5=MAX(C5:E5),$D$1,IF(E5=MAX(C5:E5),$E$1,"")))
G6	=IF(C6=MAX(C6:E6),$C$1,IF(D6=MAX(C6:E6),$D$1,IF(E6=MAX(C6:E6),$E$1,"")))
G7	=IF(C7=MAX(C7:E7),$C$1,IF(D7=MAX(C7:E7),$D$1,IF(E7=MAX(C7:E7),$E$1,"")))

 

[dispelthemyth]

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>

Spreadsheet Formulas

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

 

[circledchicken]

Hi,

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

Excel 2013

	A	B	C	D	E
1	Known	A	B
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
10
11	Result row	2
12	Result max value	2
13	Result column header	X

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

Sheet1​

Worksheet Formulas Cell Formula 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 Cell Formula 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]

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

 

[Aladin Akyurek]

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

 

[Disturbed]

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]

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]

A and B do not occur more than once.

 

[Aladin Akyurek]

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:

=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))),"")