Finding Cell Address for Use as Formula Argument
 

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?

Finding Cell Address for Use as Formula Argument
 

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?

Finding Cell Address for Use as Formula Argument
 

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?

Finding Cell Address for Use as Formula Argument
 

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?

Finding Cell Address for Use as Formula Argument
 

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