Hlookup for max value

[Pow3R]

Hello all, I have a range of data as table below and looking for the max cost for each item.
Wondering how can I use HLOOKUP to get the max cost.
Thanks for advance!

[TABLE="width: 448"]
<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Item A[/TD]
[TD="class: xl63, width: 64"]Item B[/TD]
[TD="class: xl63, width: 64"]Item C[/TD]
[TD="class: xl63, width: 64"]Item A[/TD]
[TD="class: xl63, width: 64"]Item D[/TD]
[TD="class: xl63, width: 64"]Item B[/TD]
[/TR]
[TR]
[TD="class: xl63"]Cost 1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]20[/TD]
[/TR]
[TR]
[TD="class: xl63"]Cost 2[/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]22[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
</tbody>[/TABLE]


Result
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Item A[/TD]
[TD="width: 64"]Item B[/TD]
[/TR]
[TR]
[TD]Cost 1[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]20[/TD]
[/TR]
[TR]
[TD]Cost 2[/TD]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"]8[/TD]
[/TR]
</tbody>[/TABLE]

 

[jarjarbingie]

Hello all, I have a range of data as table below and looking for the max cost for each item.
Wondering how can I use HLOOKUP to get the max cost.
Thanks for advance!

[TABLE="width: 448"]
<tbody>[TR]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"]Item A[/TD]
[TD="class: xl63, width: 64"]Item B[/TD]
[TD="class: xl63, width: 64"]Item C[/TD]
[TD="class: xl63, width: 64"]Item A[/TD]
[TD="class: xl63, width: 64"]Item D[/TD]
[TD="class: xl63, width: 64"]Item B[/TD]
[/TR]
[TR]
[TD="class: xl63"]Cost 1[/TD]
[TD="class: xl63"]2[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl63"]10[/TD]
[TD="class: xl63"]9[/TD]
[TD="class: xl63"]20[/TD]
[/TR]
[TR]
[TD="class: xl63"]Cost 2[/TD]
[TD="class: xl63"]11[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]22[/TD]
[TD="class: xl63"]4[/TD]
[TD="class: xl63"]8[/TD]
[TD="class: xl63"]5[/TD]
[/TR]
</tbody>[/TABLE]


Result
[TABLE="width: 192"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Item A[/TD]
[TD="width: 64"]Item B[/TD]
[/TR]
[TR]
[TD]Cost 1[/TD]
[TD="class: xl65"]10[/TD]
[TD="class: xl65"]20[/TD]
[/TR]
[TR]
[TD]Cost 2[/TD]
[TD="class: xl65"]11[/TD]
[TD="class: xl65"]8[/TD]
[/TR]
</tbody>[/TABLE]

B8 = MAX(IF($B$1:$G$1=B$7,$B2:$G2,0)) press CTRL+SHIFT+ENTER

where B7=Item A

 

[Pow3R]

B8 = MAX(IF($B$1:$G$1=B$7,$B2:$G2,0)) press CTRL+SHIFT+ENTER

where B7=Item A

Hi jarjar, thanks for your reply.
If lets say I have N cost, and i am only looking for the max "cost N-40" for each item, may i know how should i do?

 

[Aladin Akyurek]

What does "cost N-40" mean exactly?

 

[jarjarbingie]

Hi jarjar, thanks for your reply.
If lets say I have N cost, and i am only looking for the max "cost N-40" for each item, may i know how should i do?

not sure I understand but try this:

=MAX(IF($B$1:$G$1=B$7,$B2:$G2-40,0)) press CTRL+SHIFT+ENTER

 

[Pow3R]

Sorry for being unclear.
Lets say i have "cost 1-100" under column A and i am only looking for max "cost 40" for each item.

 

[Aladin Akyurek]

Sorry for being unclear.
Lets say i have "cost 1-100" under column A and i am only looking for max "cost 40" for each item.

Let's keep it 4 (you can generalize the solution yourself to any n (of N).

Your sample lists cost 1, cost 2, under Item A. Are you interested in the max of the first 4 cost 1 values or what?

 

[Pow3R]

Hihi, I am interested in the maximum cost 4 for item A to item C.

 

[Aladin Akyurek]

Hihi, I am interested in the maximum cost 4 for item A to item C.

In order to exclude misunderstings to a maximum, please try to post a sample with the output that goes with it, obeying "the maximum cost 4 for item A to item C.

 

[Pow3R]

Hi Aladin,
Sorry for being unclear.
here's the sample for better understanding.

[TABLE="width: 448"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD][/TD]
[TD]Item A[/TD]
[TD]Item B[/TD]
[TD]Item C[/TD]
[TD]Item A[/TD]
[TD]Item C[/TD]
[TD]Item D[/TD]
[/TR]
[TR]
[TD]Cost 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cost 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cost 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cost 4[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]29[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]32[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 6"]Looking for the maximum value of "cost 4" for each item[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Result[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Item A[/TD]
[TD]Item B[/TD]
[TD]Item C[/TD]
[TD]Item D[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Cost 4[/TD]
[TD="align: right"]40[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]32[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]