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

 

[Aladin Akyurek]

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

 

[Aladin Akyurek]

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

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.

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

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.
.