Lookup, Max, Array

[Guest]

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

 

[Guest]

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.

 

[Cutter]

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

 

[Guest]

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

 

[Biff]

Another one:

E1 = 107

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

Biff

 

[Biff]

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

Biff

 

[Guest]

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?

 

[Guest]

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

-Squeaky