XL2002 - OFFSET function and LARGE function

[Trevor Williams]

Hi All

I'm trying to return a value using the OFFSET funtion based on the LARGEst
number found in the Offset reference...

something like

=OFFSET(LARGE(B1:B3,1),0,-1)

should return "Item 3" as it has the largest value

A B
1 Item 1 6
2 Item 2 2
3 Item 3 9

Is there a way to do it? Have I just got my syntax wrong?

Thanks

Trevor Williams

 

[Bob Phillips]

=INDEX(A:A,MATCH(MAX(B:B),B:B,0))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Trevor Williams]

Brilliant, thanks Bob.
Not just my syntax, but completely the wrong function!

Regards

Trevor

 

[Bob Phillips]

You can do it with OFFSET

=OFFSET(A1,MATCH(LARGE(B:B,1),B:B,0)-1,0)

but as OFFSET is volatile, INDEX is better IMO

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)