# 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.`