Lookup, Max, Array

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

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

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.

"Squeaky" wrote:

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

Use 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(B2:D2) 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?


"Squeaky" wrote:

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

Another one:

E1 = 107

=MAX(IF(A2:A6=E1,B2:D6))

Biff

"Squeaky" <Squeaky@discussions.microsoft.com> wrote in message
news:85F7FE74-EC60-4293-83E8-69FB0950187C@microsoft.com...
>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 "Prices". 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

BTW, this is an array formula. It needs to be entered using the key combo of
CTRL.SHIFT,ENTER.

Biff

"Biff" <biffinpitt@comcast.net> wrote in message
news:O3JuM0zXGHA.4652@TK2MSFTNGP04.phx.gbl...
> Another one:
>
> E1 = 107
>
> =MAX(IF(A2:A6=E1,B2:D6))
>
> Biff
>
> "Squeaky" <Squeaky@discussions.microsoft.com> wrote in message
> news:85F7FE74-EC60-4293-83E8-69FB0950187C@microsoft.com...
>>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 "Prices". 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
>
>

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?


"Biff" wrote:

> Another one:
>
> E1 = 107
>
> =MAX(IF(A2:A6=E1,B2:D6))
>
> Biff
>
> "Squeaky" <Squeaky@discussions.microsoft.com> wrote in message
> news:85F7FE74-EC60-4293-83E8-69FB0950187C@microsoft.com...
> >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 "Prices". 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
>
>
>

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

-Squeaky

"Squeaky" wrote:

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