Offset

[namluke]

Im having trouble offseting the below

=SMALL($D$6:$E$80,ROW()-5)

Im looking to make it jump 3 coloums to the left.

Its just not working though.

This is how i wrote it

=offset(SMALL($D$6:$E$80,ROW()-5)),0,-3)

Any ideas whats wrong?

[ranman256]

offset has only 2 params: offset(row,col)

[XOR LX]

You can only offset a range reference, not a value (which will be the result of the SMALL function).

What exactly is the purpose of the SMALL function in there? Are you attempting to offset the cell address which contains the smallest value in that range?

Regards

[namluke]

Hi thanks for your reply, who would that look in the above formula?

[namluke]

yep thats what im trying to achieve.

[XOR LX]

I think you need to:

a) Clarify to whom you're replying in each of your posts (if it's not clear)
b) Attach an actual workbook with some examples and your desired results clearly outlined

Regards

[namluke]

Sorry about that, it was aimed at yourself XOR LX.

I have attached the sheet.

I hope that helps.

[XOR LX]

Thanks.

So what exactly are you trying to return, and in which cells?

Regards

[namluke]

Ahh i put the wrong version!!

Looking at the formula in H6 then offset that to where the cell come from.

So in G6 it would bring back 15518 which is from cell B40.

[XOR LX]

I notice that your SMALL is looking at both columns C and D - are you saying that it can come from either?

So that sometimes you'll be offsetting 1 column to the left (if it's in column C) and sometimes 2 (if it's in column D)?

Should it not just be one or the other, i.e. either column C or column D, but not both? Can you clarify?

Regards

[namluke]

I see what your saying, if it can be just from column C for now that would be great!

[XOR LX]

In G6 and copy down:

=INDEX($B$6:$B$80,MATCH(SMALL($C$6:$C$80,ROWS($1:1)),$C$6:$C$80,0))

Regards

[newdoverman]

I'm having trouble with your example matching it with your description. I understand that you want to find the lowest value in the range D6:E80 and return the value that is 3 columns to the left of that lowest value.

This formula will do that. Array enter (Ctrl + Shift + Enter) where you want the answer:

Please Login or Register  to view this content.

If you are only wanting the value 2 columns over from the smallest value in the range D6:E80 then change the -3 to -2.

If you are only interested in the small value in column D, change the range D6:E80 to D6:D80

[namluke]

Thanks guys, been a great help!