XL2002 - OFFSET function and LARGE function
 

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

XL2002 - OFFSET function and LARGE function
 

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

XL2002 - OFFSET function and LARGE function
 

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

Regards

Trevor

"Bob Phillips" wrote:

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

XL2002 - OFFSET function and LARGE function
 

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)



"Trevor Williams" wrote in
message ...
Brilliant, thanks Bob.
Not just my syntax, but completely the wrong function!

Regards

Trevor

"Bob Phillips" wrote:

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