Lookup value

[naghy]

I have two colums, one with prices one with quantities

i want to lookup the max quantity and have the price for that quantity shown
in another cell
but the problem is that there are 2 max quantities, so the lookup formula
results in 0

how do i resolve this?

[Jason Morin]

With prices in col. B and quantities in col. A, this'll
return the price for the 1st max qty found in col. A:

=INDEX(B:B,MATCH(MAX(A:A),A:A,0))

HTH
Jason
Atlanta, GA

>-----Original Message-----
>I have two colums, one with prices one with quantities
>
>i want to lookup the max quantity and have the price for
that quantity shown
>in another cell
>but the problem is that there are 2 max quantities, so
the lookup formula
>results in 0
>
>how do i resolve this?
>.
>

[Aladin Akyurek]

naghy wrote:
> I have two colums, one with prices one with quantities
>
> i want to lookup the max quantity and have the price for that quantity shown
> in another cell
> but the problem is that there are 2 max quantities, so the lookup formula
> results in 0
>
> how do i resolve this?

If you'd like to retrieve the associated prices for all max value instances:

http://tinyurl.com/562xz

[Jason Morin]

That's a lot of formulas. Why not just keep it simple
with:

=INDEX(B1:B10,SMALL(IF(A1:A10=MAX(A1:A10),ROW(INDIRECT
("1:10"))),C1))

Array-entered, where C1 = nth occurrence of the max value.

Jason

>-----Original Message-----
>naghy wrote:
>> I have two colums, one with prices one with quantities
>>
>> i want to lookup the max quantity and have the price
for that quantity shown
>> in another cell
>> but the problem is that there are 2 max quantities, so
the lookup formula
>> results in 0
>>
>> how do i resolve this?
>
>If you'd like to retrieve the associated prices for all
max value instances:
>
>http://tinyurl.com/562xz
>.
>

[Aladin Akyurek]

Jason Morin wrote:
> That's a lot of formulas. Why not just keep it simple
> with:
>
> =INDEX(B1:B10,SMALL(IF(A1:A10=MAX(A1:A10),ROW(INDIRECT
> ("1:10"))),C1))
>
> Array-entered, where C1 = nth occurrence of the max value.

[...]

>>If you'd like to retrieve the associated prices for all
>
> max value instances:
>
>>http://tinyurl.com/562xz

Did you try it? It's a formula system for constructing a Top N list with
a pretty efficient temporal profile. With regard to OP's question, which
I took to be:

Price Qty
2.4 20
3.5 30
2.6 20
3.8 25
2.9 30
3.1 22
3.4 30
3.3 27
2.1 30
2.7 24

with as result: 3.5, 2.9, 3.4, and 2.1.

[Jason Morin]

I didn't say that it didn't work...I just think it's
overkill. Keep it simple. I constantly have to remind
myself of that when I write formulas and post solutions.

Jason

>-----Original Message-----
>Jason Morin wrote:
>> That's a lot of formulas. Why not just keep it simple
>> with:
>>
>> =INDEX(B1:B10,SMALL(IF(A1:A10=MAX(A1:A10),ROW(INDIRECT
>> ("1:10"))),C1))
>>
>> Array-entered, where C1 = nth occurrence of the max
value.
>
>[...]
>
>>>If you'd like to retrieve the associated prices for
all
>>
>> max value instances:
>>
>>>http://tinyurl.com/562xz
>
>Did you try it? It's a formula system for constructing a
Top N list with
>a pretty efficient temporal profile. With regard to OP's
question, which
>I took to be:
>
>Price Qty
>2.4 20
>3.5 30
>2.6 20
>3.8 25
>2.9 30
>3.1 22
>3.4 30
>3.3 27
>2.1 30
>2.7 24
>
>with as result: 3.5, 2.9, 3.4, and 2.1.
>.
>

[Aladin Akyurek]

[1] If my construal of OP's question is right (you seem to agree), your
suggestion would not compute the result list from the sample I provided.
That's why I asked whether you had tried it.

[2] A single formula does not always mean "simple". Why would

=INDEX(B1:B10,SMALL(IF(A1:A10=MAX(A1:A10),ROW(INDIRECT("1:10"))),C1))

be considered simple, regardless what it is meant to calculate, while
the formula includes:

ROW(INDIRECT("1:10"))

something that requires a fair bit knowledge in order to adapt?

[3] Last but not least: A single formula is not always the most
efficient way to bring about a solution.

Jason Morin wrote:
> I didn't say that it didn't work...I just think it's
> overkill. Keep it simple. I constantly have to remind
> myself of that when I write formulas and post solutions.
>
> Jason

>
>
>>-----Original Message-----
>>Jason Morin wrote:
>>
>>>That's a lot of formulas. Why not just keep it simple
>>>with:
>>>
>>>=INDEX(B1:B10,SMALL(IF(A1:A10=MAX(A1:A10),ROW(INDIRECT
>>>("1:10"))),C1))
>>>
>>>Array-entered, where C1 = nth occurrence of the max
>
> value.
>
>>[...]
>>
>>
>>>>If you'd like to retrieve the associated prices for
>
> all
>
>>>max value instances:
>>>
>>>
>>>>http://tinyurl.com/562xz
>>
>>Did you try it? It's a formula system for constructing a
>
> Top N list with
>
>>a pretty efficient temporal profile. With regard to OP's
>
> question, which
>
>>I took to be:
>>
>>Price Qty
>>2.4 20
>>3.5 30
>>2.6 20
>>3.8 25
>>2.9 30
>>3.1 22
>>3.4 30
>>3.3 27
>>2.1 30
>>2.7 24
>>
>>with as result: 3.5, 2.9, 3.4, and 2.1.
>>.
>>