Part 3, Formula help

G

Guest

Sheet 1:

A H J

JAESTHJ 39052 356.57
JAESTHJ 38718 536.83
JAESTHJ 38808 473.43
JAFRAFJ 39052 342.99
JAFRAFJ 38718 1516.58
JAFRAFJ 38808 1499.16

Sheet 2

A D

JAESTHJ want to equal 473.43
JAFRAFJ want to equal 1499.16

ignore any 39052 values in the MAX function.


........

Coincidentally on Sheet 1 Column H I have had to use a number format of a
date for the MAX function to work.
If at all possible I would like the MAX function to work on the following:-

Sheet 1

A Replace H values above with:-

JAESTHJ JAESTHJ0600 356.57
JAESTHJ JAESTHJ0601 536.83
JAESTHJ JAESTHJ0602 473.43
JAFRAFJ JAFRAFJ0600 342.99
JAFRAFJ JAFRAFJ0601 1516.58
JAFRAFJ JAFRAFJ0602 1499.16

On JAESTHJ the MAX value = JAESTHJ0602 :. 473.43



Thank for your patience and time.

Matthew Balch
 
B

Bob Phillips

I think this is what you need, but I still don't get the coincidentally bit
(sorry!)

=INDEX(Sheet1!$J$1:$J$6,MATCH(1,(Sheet1!$A$1:$A$6=A8)*(MAX(IF((Sheet1!$A$1:$
A$6=A8)*(Sheet1!$H$1:$H$6<>39052),Sheet1!$H$1:$H$6))=Sheet1!$H$1:$H$6),0))

it is an array formula

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
Top