# Using a cell ref. from =address in an array formula

#### SamueXCEl

##### New Member
Hi - I am trying to do a nested iferror/index/match/countif array formula - my formula works - but when I use a reference made from =address, I can't get it to work.

The =address reference is the max point for searching in my formula - it would be repeated a few times.

any ideas?

just a simple example:
e.g. instead of doing =sum(a1:a5) I am trying to do =sum(a1:the cell where I have made my =address cell reference - a???)

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Eric W

##### MrExcel MVP
Welcome to the forum.

It's hard to say what will work for you without seeing your formula, there are many ways to do what you ask. One thing you can try is to use INDEX instead of ADDRESS. For example:

=SUM(A1:INDEX(A:A,MATCH("Z",B1:B100,0")))

The MATCH finds the cell in B1:B100 with a Z in it, the INDEX finds the corresponding cell in A, then the A1:Axx range is used by SUM. So if you can find your end cell with something like that, you might be in luck.

#### SamueXCEl

##### New Member
Thanks Eric - here is my formula - to return the unique/remove duplicates from column area H2 to the address I have created:
{=IFERROR(INDEX(\$H\$2:[], MATCH(0, COUNTIF(\$B1:\$D1, \$H\$2:[]&"")+IF(\$H\$2:[]="", 1, 0)),"")

[] is where I assume the reference from =ADDRESS would go.

#### Eric W

##### MrExcel MVP
I had a bit of an issue getting your original formula to work. I had to tweak it a bit. You may want to look at it to make sure it does what you expect. It has a fixed end row of H15. Having gotten it to work, I changed it so that the bottom row of the range being search is based on the row number in F1. You didn't say how you determined the last row. If you're just looking for the last row with data, consider the formula in F2.

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Your original (ish) formula is in B2. The updated version is in E2. The F2 formula gets you the last used row in H, which you can incorporate in E2 if you wish. If you absolutely want to use ADDRESS, the J2 formula has that. But I don't recommend it since INDIRECT is volatile and INDEX is not.

Let me know if this helps.

#### SamueXCEl

##### New Member

Hi - no, my formulas works grand when I [Ctrl.]+[Shift]+[Enter] it to remove the duplicates. I just wanted to determine the area of column H (how far down it goes) when removing the duplicates.

I used a formula to find the first empty cell in column H, and just used that row number - 1 as the last row, I know the column letter, so I made a cell reference, and this cell ref. should be the extreme of the 'H' components for my master formula.

Basically, when a bigger/smaller data source file gets dropped into the file next month, the formula will then know the end of the H column area to look in, and only look within this variable boundary area each time.

I can only get it to work when I set it to an actual area - e.g. H2:H15 - but that only works when the raw data I need is within that area, anything else is not necessary and ruins my result.

Is it possible to use a cell reference from an =address formula as the 2nd part to the ':', instead of - e.g. - H15?

#### Eric W

##### MrExcel MVP
Yes, the J2 formula in my previous post shows how to use INDIRECT(ADDRESS to reference the bottom row. But look at the E2 formula using INDEX - it's shorter and more efficient.

Last edited:

#### SamueXCEl

##### New Member

Thanks Eric - let me try ...

#### SamueXCEl

##### New Member
Thanks Eric - I used your INDEX hint. Worked.

#### mikerickson

##### MrExcel MVP
Would Names work.
If you have a named cell MyCell and use =SUM(A1:MyCell) you could adjust the definition of MyCell and the formula(s) will follow.

Replies
7
Views
102
Replies
2
Views
94
Replies
4
Views
98
Replies
4
Views
61
Replies
10
Views
135

1,129,312
Messages
5,635,492
Members
416,860
Latest member
coen078

### 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