Part 3, Formula help
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
|