Formula help

[Guest]

Posed this question yesterday. Bob replied but need more help. Driving me
insane (can normally figure out formulas!)


Bobs reply:-

=INDEX('Issue Analysis'!J1:J1000,MATCH(MAX(IF('Issue
Analysis'!J1:J1000<>0,('Issue Analysis'!H1:H1000))),'Issue
Analysis'!H1:H1000,0))

What I've got so far:-

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

I would now like to add the MAX part of the formula (column J) as
highlighted in Bobs formula.

I would like to, if possible, stay away from array formulas as these seem to
creat lag issues within excel (Why?) and also other users will not know how
to set them with ctrl+shift+enter etc

Thanks in advance

 

[paul.robinson]

Hi
could you put this post on the end of the previous thread. Taken on its
own, the answer to this post is to be to copy and paste bits of Bob's
formula into yours!
regards
Paul

 

[Bob Phillips]

I still don't get what you want. Can you post some data in a message and
some results so that we can see better what is required.

--
HTH

Bob Phillips

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

 

[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

 

[Tom Ogilvy]

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

entered with Ctrl+Shift+enter rather than just enter since this is an array
formula.