I would like to find a number in an array, then use the cell address of that
number to pull out more info from that row. Here's an example:

A B C D
1 Part # Cost Use Total (= Cost x Use)
2 A1 $5 100 $500
3 A2 $7.50 10 $ 75
4 A3 $100 8 $800

I would like to find the part number of the highest total use. In this
example, that's part A3. I'd like to search using the =LARGE function, find
the highest values, and use the addresses of those values to find part
numbers and other information on those parts.

Any suggestions?

With your data in A2:D4 as shown here this will return the part number
of the part with the highest total:
=INDEX($A$2:$D$4,MATCH(MAX($D$2:$D$4),$D$2:$D$4,0) ,1)

Change the last argument to return data from other colums i.e
=INDEX($A$2:$D$4,MATCH(MAX($D$2:$D$4),$D$2:$D$4,0) ,2)
will return the Cost

Hope this helps
Rowan.

pdberger wrote:
I would like to find a number in an array, then use the cell address of that
number to pull out more info from that row. Here's an example:

A B C D
1 Part # Cost Use Total (= Cost x Use)
2 A1 $5 100 $500
3 A2 $7.50 10 $ 75
4 A3 $100 8 $800

I would like to find the part number of the highest total use. In this
example, that's part A3. I'd like to search using the =LARGE function, find
the highest values, and use the addresses of those values to find part
numbers and other information on those parts.

Any suggestions?

Hi!

To return the part # that corresponds to the highest total:

=INDEX(A2:A100,MATCH(MAX(D2:D100),D2:D100,0))

However, if there are 2 or more items with the same highest total the
formula will return ONLY the first instance.

For example, part #'s A1 and A3 BOTH have a total of $800. The formula will
return part # A1.

One way to compensate for this is to rank the totals then use that rank to
find the corresponding part #.

Post back if that is a possibilty and you need further assistance.

Biff

"pdberger" wrote in message
...
I would like to find a number in an array, then use the cell address of
that
number to pull out more info from that row. Here's an example:

A B C D
1 Part # Cost Use Total (= Cost x Use)
2 A1 $5 100 $500
3 A2 $7.50 10 $ 75
4 A3 $100 8 $800

I would like to find the part number of the highest total use. In this
example, that's part A3. I'd like to search using the =LARGE function,
find
the highest values, and use the addresses of those values to find part
numbers and other information on those parts.

Any suggestions?

I put your data in A1:C4.
In H2 I have =INDEX($A$2:$A$4,MATCH(LARGE($C$2:$C$4,1),$C$2:$C$ 4,0)) and it
returns part-no A1
IN H3 I have =INDEX($A$2:$A$4,MATCH(LARGE($C$2:$C$4,2),$C$2:$C$ 4,0)) and it
returns part-no A2
If I increase sales of A2 to 120, the formulas return the correct values

MATCH on it own, tells me in what row the large-value occurs; note the 0 as
last argument in LARGE - this permits the data to be in any order.
INDEX is given an array and a row number and picks out the part-no. (there
is another syntax for INDEX but this one works here)

Of course, if I enter 1,2,3 .. in l2, L3..... I could replace the formula in
H2 by
=INDEX($A$2:$A$4,MATCH(LARGE($C$2:$C$4,L2),$C$2:$C $4,0)) and this would
making copying easier since I would not need to edit the second argument of
LARGE.

best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pdberger" wrote in message
...
I would like to find a number in an array, then use the cell address of
that
number to pull out more info from that row. Here's an example:

A B C D
1 Part # Cost Use Total (= Cost x Use)
2 A1 $5 100 $500
3 A2 $7.50 10 $ 75
4 A3 $100 8 $800

I would like to find the part number of the highest total use. In this
example, that's part A3. I'd like to search using the =LARGE function,
find
the highest values, and use the addresses of those values to find part
numbers and other information on those parts.

Any suggestions?

=INDIRECT("A"&1+MATCH(LARGE(D2:D4,1),D2:D4,0))
will return "A3" (The value in A4)
=INDIRECT("A"&1+MATCH(LARGE(D2:D4,2),D2:D4,0))
will return "A1" (The value in A2)
=INDIRECT("A"&1+MATCH(LARGE(D2:D4,3),D2:D4,0))
will return "A2" (THe value in A3)

"pdberger" wrote:

I would like to find a number in an array, then use the cell address of that
number to pull out more info from that row. Here's an example:

A B C D
1 Part # Cost Use Total (= Cost x Use)
2 A1 $5 100 $500
3 A2 $7.50 10 $ 75
4 A3 $100 8 $800

I would like to find the part number of the highest total use. In this
example, that's part A3. I'd like to search using the =LARGE function, find
the highest values, and use the addresses of those values to find part
numbers and other information on those parts.

Any suggestions?