I posted the question yesterday and got a reply from Bob. Thanks by the way.

Two points:-

The formula proivded was:-

=INDEX(J1:J1000,MATCH(MAX(IF(J1:J1000<0,(H1:H1000 ))),H1:H1000,0))

But I need a lookup function within this.

To reiterate the scenario:-

Sheet 1
Column:A = look up reference
Column:H = the MAX reference
Column:J = the value I wish to lookup

Sheet 2
Coulmn:A - look up reference

Say in cell E2 I want to lookup the reference in cell A2 on Sheet 1 that is:-

A2 = relevant line in column A that is the MAX of column H and return the
value of Column J.

Thanks in advance.


Matthew Balch