INDEX, MAX, OFFSET, MATCH
 

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

INDEX, MAX, OFFSET, MATCH
 

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.

"Joe Gieder" wrote:

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

INDEX, MAX, OFFSET, MATCH
 

This worked great. Thank you.

"Toppers" wrote:

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.

"Joe Gieder" wrote:

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