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!

	Item A	Item B	Item C	Item A	Item D	Item B
Cost 1	2	4	5	10	9	20
Cost 2	11	8	22	4	8	5

<colgroup><col width="64" span="7" style="width:48pt"> </colgroup><tbody>
</tbody>

Result

	Item A	Item B
Cost 1	10	20
Cost 2	11	8

<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[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!

	Item A	Item B	Item C	Item A	Item D	Item B
Cost 1	2	4	5	10	9	20
Cost 2	11	8	22	4	8	5

<tbody>
</tbody>

Result

	Item A	Item B
Cost 1	10	20
Cost 2	11	8

<tbody>
</tbody>

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.

	Item A	Item B	Item C	Item A	Item C	Item D
Cost 1
Cost 2
Cost 3
Cost 4	40	10	29	38	15	32
Looking for the maximum value of "cost 4" for each item
Result
	Item A	Item B	Item C	Item D
Cost 4	40	10	15	32

<colgroup><col span="7"></colgroup><tbody>
</tbody>