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

What I've got so far:-

=INDEX('Issue Analysis'!$A$3:$J$15000,MATCH($A8,'Issue
Analysis'!$A$3:$A$15000,0),10)



Thank for your patience and time.

Matthew Balch

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)

"Matthew Balch" wrote in message
...
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

What I've got so far:-

=INDEX('Issue Analysis'!$A$3:$J$15000,MATCH($A8,'Issue
Analysis'!$A$3:$A$15000,0),10)



Thank for your patience and time.

Matthew Balch