software

I get lot of help from you all reading these postings. I'm a bit stuck on
this one.
I have a spreadsheet containing part numbers and prices. Since this
spreadsheet is created from a database I am unable to modify it. This
spreadsheet is called quot;Pricesquot;. On many occasions the same part number (in
col A) is repeated up to 5 times with up to 3 different prices on collumns b,
c d. (see ref). On another sheet in the same workbook I want to be able to
look up a given part number, and have the highest price returned. (For
Part=107 Price=$1.25, For Part=111 Price=$2.05)

Part# Price1 Price2 Price3
107 $.50 $.75 $.50
107 $.50 $1.25 $.50
111 $1.50 $1.50 $1.75
111 $1.55 $1.50 $0.00
111 $2.05 $1.50 $1.75

Thanks!
Squeaky

This adds some complexity, but it may do the trick:
1.- Create a new column at the end to get the max of the three prices
2.- Create a pivot table over the whole range, including the part# and the
max of the prices
3.- In the layout, just drag the part# on the rows and the max of the column
with the maximum in the data section.
4.- If you need this info outside the table, use GETPIVOTDATA() over the table

Miguel.

quot;Squeakyquot; wrote:

gt; I get lot of help from you all reading these postings. I'm a bit stuck on
gt; this one.
gt; I have a spreadsheet containing part numbers and prices. Since this
gt; spreadsheet is created from a database I am unable to modify it. This
gt; spreadsheet is called quot;Pricesquot;. On many occasions the same part number (in
gt; col A) is repeated up to 5 times with up to 3 different prices on collumns b,
gt; c d. (see ref). On another sheet in the same workbook I want to be able to
gt; look up a given part number, and have the highest price returned. (For
gt; Part=107 Price=$1.25, For Part=111 Price=$2.05)
gt;
gt; Part# Price1 Price2 Price3
gt; 107 $.50 $.75 $.50
gt; 107 $.50 $1.25 $.50
gt; 111 $1.50 $1.50 $1.75
gt; 111 $1.55 $1.50 $0.00
gt; 111 $2.05 $1.50 $1.75
gt;
gt; Thanks!
gt; Squeaky


If your part numbers are sorted so that all matches are together then
you can try something like this:

Assuming your data starts in A2

=MAX(OFFSET(A1,MATCH(G1,A2:A50,0),1,COUNTIF(A2:A50 ,G1),3))

The part number to be checked is in G1 for this sample formula--
Cutter
------------------------------------------------------------------------
Cutter's Profile: www.excelforum.com/member.php...foamp;userid=9848
View this thread: www.excelforum.com/showthread...hreadid=532767Use the following conditional array function (below) in any cell you wish
(you will need to add a column to your list that finds the max for each
record (ie. =MAX(B22) in row 2), in order for this array function to work.

The curly brackets around the array function let you know that it is an
array function. After you've entered the formula (without curly brackets)
press Ctrl Shift Enter and the curly bracket will appear. The example below
applies to Part# 107)

{=MAX(IF($A$2:$A$6=107,E2:E6,0))}
--
Hope this Helps!
Camilo

Objective: To help one person each day!

Feedback is greatly appreciated! Please let me know if I've answered your
question or if my post was helpful to you?quot;Squeakyquot; wrote:

gt; I get lot of help from you all reading these postings. I'm a bit stuck on
gt; this one.
gt; I have a spreadsheet containing part numbers and prices. Since this
gt; spreadsheet is created from a database I am unable to modify it. This
gt; spreadsheet is called quot;Pricesquot;. On many occasions the same part number (in
gt; col A) is repeated up to 5 times with up to 3 different prices on collumns b,
gt; c d. (see ref). On another sheet in the same workbook I want to be able to
gt; look up a given part number, and have the highest price returned. (For
gt; Part=107 Price=$1.25, For Part=111 Price=$2.05)
gt;
gt; Part# Price1 Price2 Price3
gt; 107 $.50 $.75 $.50
gt; 107 $.50 $1.25 $.50
gt; 111 $1.50 $1.50 $1.75
gt; 111 $1.55 $1.50 $0.00
gt; 111 $2.05 $1.50 $1.75
gt;
gt; Thanks!
gt; Squeaky

Another one:

E1 = 107

=MAX(IF(A2:A6=E1,B26))

Biff

quot;Squeakyquot; gt; wrote in message
...
gt;I get lot of help from you all reading these postings. I'm a bit stuck on
gt; this one.
gt; I have a spreadsheet containing part numbers and prices. Since this
gt; spreadsheet is created from a database I am unable to modify it. This
gt; spreadsheet is called quot;Pricesquot;. On many occasions the same part number (in
gt; col A) is repeated up to 5 times with up to 3 different prices on collumns
gt; b,
gt; c d. (see ref). On another sheet in the same workbook I want to be able to
gt; look up a given part number, and have the highest price returned. (For
gt; Part=107 Price=$1.25, For Part=111 Price=$2.05)
gt;
gt; Part# Price1 Price2 Price3
gt; 107 $.50 $.75 $.50
gt; 107 $.50 $1.25 $.50
gt; 111 $1.50 $1.50 $1.75
gt; 111 $1.55 $1.50 $0.00
gt; 111 $2.05 $1.50 $1.75
gt;
gt; Thanks!
gt; Squeaky
BTW, this is an array formula. It needs to be entered using the key combo of
CTRL.SHIFT,ENTER.

Biff

quot;Biffquot; gt; wrote in message
...
gt; Another one:
gt;
gt; E1 = 107
gt;
gt; =MAX(IF(A2:A6=E1,B26))
gt;
gt; Biff
gt;
gt; quot;Squeakyquot; gt; wrote in message
gt; ...
gt;gt;I get lot of help from you all reading these postings. I'm a bit stuck on
gt;gt; this one.
gt;gt; I have a spreadsheet containing part numbers and prices. Since this
gt;gt; spreadsheet is created from a database I am unable to modify it. This
gt;gt; spreadsheet is called quot;Pricesquot;. On many occasions the same part number
gt;gt; (in
gt;gt; col A) is repeated up to 5 times with up to 3 different prices on
gt;gt; collumns b,
gt;gt; c d. (see ref). On another sheet in the same workbook I want to be able
gt;gt; to
gt;gt; look up a given part number, and have the highest price returned. (For
gt;gt; Part=107 Price=$1.25, For Part=111 Price=$2.05)
gt;gt;
gt;gt; Part# Price1 Price2 Price3
gt;gt; 107 $.50 $.75 $.50
gt;gt; 107 $.50 $1.25 $.50
gt;gt; 111 $1.50 $1.50 $1.75
gt;gt; 111 $1.55 $1.50 $0.00
gt;gt; 111 $2.05 $1.50 $1.75
gt;gt;
gt;gt; Thanks!
gt;gt; Squeaky
gt;
gt;
Biff go it!
--
Hope this Helps!
Camilo

Objective: To help one person each day!

Feedback is greatly appreciated! Please let me know if I've answered your
question or if my post was helpful to you?quot;Biffquot; wrote:

gt; Another one:
gt;
gt; E1 = 107
gt;
gt; =MAX(IF(A2:A6=E1,B26))
gt;
gt; Biff
gt;
gt; quot;Squeakyquot; gt; wrote in message
gt; ...
gt; gt;I get lot of help from you all reading these postings. I'm a bit stuck on
gt; gt; this one.
gt; gt; I have a spreadsheet containing part numbers and prices. Since this
gt; gt; spreadsheet is created from a database I am unable to modify it. This
gt; gt; spreadsheet is called quot;Pricesquot;. On many occasions the same part number (in
gt; gt; col A) is repeated up to 5 times with up to 3 different prices on collumns
gt; gt; b,
gt; gt; c d. (see ref). On another sheet in the same workbook I want to be able to
gt; gt; look up a given part number, and have the highest price returned. (For
gt; gt; Part=107 Price=$1.25, For Part=111 Price=$2.05)
gt; gt;
gt; gt; Part# Price1 Price2 Price3
gt; gt; 107 $.50 $.75 $.50
gt; gt; 107 $.50 $1.25 $.50
gt; gt; 111 $1.50 $1.50 $1.75
gt; gt; 111 $1.55 $1.50 $0.00
gt; gt; 111 $2.05 $1.50 $1.75
gt; gt;
gt; gt; Thanks!
gt; gt; Squeaky
gt;
gt;
gt;

Thanks for all the replies. I tried the last one from Biff. Works like a charm!

-Squeaky

quot;Squeakyquot; wrote:

gt; I get lot of help from you all reading these postings. I'm a bit stuck on
gt; this one.
gt; I have a spreadsheet containing part numbers and prices. Since this
gt; spreadsheet is created from a database I am unable to modify it. This
gt; spreadsheet is called quot;Pricesquot;. On many occasions the same part number (in
gt; col A) is repeated up to 5 times with up to 3 different prices on collumns b,
gt; c d. (see ref). On another sheet in the same workbook I want to be able to
gt; look up a given part number, and have the highest price returned. (For
gt; Part=107 Price=$1.25, For Part=111 Price=$2.05)
gt;
gt; Part# Price1 Price2 Price3
gt; 107 $.50 $.75 $.50
gt; 107 $.50 $1.25 $.50
gt; 111 $1.50 $1.50 $1.75
gt; 111 $1.55 $1.50 $0.00
gt; 111 $2.05 $1.50 $1.75
gt;
gt; Thanks!
gt; Squeaky