Excel Offset function

[dicken]

Hi,
if I want to use Offset but want to do this based on a value , so I want the cell reference containing X as my starting point,
I have tried ;

  CELL("address",INDEX($A$1:$A$13,MATCH(D1,$A$1:$A$13,0),1) )[ = $A$4

Which returns the correct cell reference but when I nest in Offset I get an error, so I'm either making a basic mistake or
I need a deferent approach?

Richard.

 

[6StringJazzer]

This formula returns the string "$A$4" which is not useful in an OFFSET formula. I think it would be better if you told us what you are trying to do, rather than showing the method that fails to do what you want to do.

Just to give you an idea, let's say you have a value in D1. You want to find the row that has that value in column A, and then return the value from column B in that same row.

=INDEX($B$1:$B$13,MATCH(D1,$A$1:$A$13,0))

OFFSET is not needed.

 

[dicken]

This formula returns the string "$A$4" which is not useful in an OFFSET formula. I think it would be better if you told us what you are trying to do, rather than showing the method that fails to do what you want to do.

Just to give you an idea, let's say you have a value in D1. You want to find the row that has that value in column A, and then return the value from column B in that same row.

=INDEX($B$1:$B$13,MATCH(D1,$A$1:$A$13,0))

OFFSET is not needed.

I'm searching for a given value and then offset from that value.
Richard.

 

[6StringJazzer]

Sorry, you have to provide a lot more detail. Where are you searching for the given value? What is the offset from that value you want to return?

Is the formula I gave remotely close to what you want? My formula finds a value in column A then returns a value that is offset one column to the right (column B). But it is done by using direct references to the column rather than OFFSET (which, BTW, is a volatile function).

 

[Joe4]

I'm searching for a given value and then offset from that value.

That is what INDEX/MATCH functions do.

It might be best if you could post some sample data and expected output.
MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[dicken]

This formula returns the string "$A$4" which is not useful in an OFFSET formula. I think it would be better if you told us what you are trying to do, rather than showing the method that fails to do what you want to do.

Just to give you an idea, let's say you have a value in D1. You want to find the row that has that value in column A, and then return the value from column B in that same row.

=INDEX($B$1:$B$13,MATCH(D1,$A$1:$A$13,0))

OFFSET is not needed.

 

[Joe4]

Looks like you just quoted a previous reply from 6StringJazzer.
Is there a question or comment there?

 

[dicken]

No, take a single column for example a to z, ok so use index match to find 'k', and then I want to
return the value that as X and Y from that point. example to return 2 rows above the value

MATCH("c",H7:H14,0)  - 2

, but I still need the column.

Richard.

 

[Joe4]

Your question/data layout is not quite clear.
I refer you back to post 5 from yesterday.

Please show us some actual sample data and your expected result.
I think it may make a lot more sense if we could see the data you are working with and exactly what you are hoping to return from it.

 

[dicken]

I might try forum then I can upload sheet, its pretty straight forward,
using offset; =OFFSET(A5,4,2,3,2)

But I don't know the cell reference but I do know the value contained within and then I need to offset from that point.