# Cell location, offset

#### MartinKI

##### New Member
I am looking for a formula which gives me the location of the cell in the current worksheet, in combination with an offset formula.
Anyone an idea how to solve this?

example:
I have 3 identical tables underneath each other, exactly similar.
in the left corner of the table is the name of that portfolio.

10 rows under that name and 1 column to the right is the total, which I am interested in.

now I am looking for a formula like this:

=offset(cell reference,9,1)
The problem (and my question) is to come up with a flexibel formula which gives me the cell reference (say A1) based on the lookup function on the name of the portfolio, which is located in the left corner.

Tx.

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Peter_SSs

##### MrExcel MVP, Moderator
Martin

Does this do what you want?
=INDEX(B:B,MATCH("portfolio name",A:A,0)+9)

#### MartinKI

##### New Member
I still need to make a step (or actually 3 steps) to the right.....

sorry....i didn't look carefully enough....I see you reffering to B:B

let me have a look

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
I still need to make a step (or actually 3 steps) to the right.....

sorry....i didn't look carefully enough....I see you reffering to B:B

let me have a look
In my formula, change B:B to the column the totals are in and change A:A to the column the portfolio names are in.

#### MartinKI

##### New Member
Yes It worked! thanks....although the column B:B needs to be a table to function properly:

my final formula:
=INDEX(B1:G45;(MATCH(A1;G1:G45;0)+8);6)

second question though.....is there a formula which simply gives me the cell location as result?? so A12 instead of the value in A12??

#### Peter_SSs

##### MrExcel MVP, Moderator
Since we appear to know what column it is in, would something like this do what you want?
="G" & MATCH(A1,G1:G45,0)+8

#### MartinKI

##### New Member
Yes, that will work if you know what column it is in...this was more meant as a general question....but tx anyway.

Perhaps then,

#### MartinKI

##### New Member
Tx...that will be helpfull in future formulas..

Replies
1
Views
445
Replies
4
Views
212
Replies
3
Views
456
Replies
3
Views
336
Replies
1
Views
253

1,190,838
Messages
5,983,179
Members
439,825
Latest member

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back