Excel - INDEX, MAX, OFFSET, MATCH

Asked By JoeGiede on 14-Jun-07 03:10 PM
I'm having trouble with this formula:
INDEX('All Data'!$J$3:$J$671,MAX(('All Data'!$J$3:$J$671),OFFSET('All
Data'!$A$3,MATCH('Summary Data'!A8,'All Data'!$A$3:$A$671,0)-1,9,-1,-1)))

What I'm trying to do is use a part number from one sheet (Summary Data A8)
and look on another (All Data) to find the MAX price for the MATCHing part
number.
the data is contained this way: 'All Data'!$J$3:$J$671 has the price,
'Summary Data'!A8 has the part number I'm using as the value to lookup, 'All
Data'!$A$3:$A$671 has the array of part numbers to look in for the MATCH. On
the All Data sheet the same part number is listed many many times and a lot
of times the price is same, example:

A         J
123     $5
123     $5
123     $6
123     $5
123     $6
987     $10
987     $11
987     $10

Can this be done?

TIA
joe




Topper replied on 14-Jun-07 03:43 PM
try:

=MAX(IF('All Data'!$A$3:$A$671='Summary Data'!A8,'All Data'!$J$3:$J$671))

Entered with Ctrl+Shift+enter

You will get curly braces {} round the formula if entered correctly.
JoeGiede replied on 15-Jun-07 08:34 AM
This worked great. Thank you.