## Get cell addresses from several cells with array formula

**Question:**

How do i get the cell adresses from the cells that contain TRUE? See picture below.

### Array formula in cell B7:

### How to create an array formula

- Copy (Ctrl + c) and paste (Ctrl + v) array formula into formula bar.
- Press and hold Ctrl + Shift.
- Press Enter once.
- Release all keys.

### Named range

tbl A1:E5

### How to create a named range

- Select cell range A1:E5
- Type tbl in name box

### How the array formula works

**Step 1 - Filter row numbers**

=ADDRESS(**SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1))**, ((SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/65536, ""), ROW(A1)))-SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1)))*65536)

SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1))

becomes

SMALL(IF({FALSE, TRUE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, TRUE;TRUE, FALSE, FALSE, FALSE, FALSE;FALSE, TRUE, FALSE, TRUE, TRUE;FALSE, FALSE, FALSE, TRUE, TRUE}=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1))

becomes

SMALL(IF({FALSE, TRUE, FALSE, FALSE, FALSE;FALSE, FALSE, FALSE, FALSE, TRUE;TRUE, FALSE, FALSE, FALSE, FALSE;FALSE, TRUE, FALSE, TRUE, TRUE;FALSE, FALSE, FALSE, TRUE, TRUE}, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1))

becomes

SMALL({"", 1, "", "", "";"", "", "", "", 2;3, "", "", "", "";"", 4, "", 4, 4;"", "", "", 5, 5}, ROW(A1))

and returns 1.

**Step 2 - Filter column numbers**

=ADDRESS(SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1)), **((SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/65536, ""), ROW(A1)))-SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1)))*65536**)

((SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/65536, ""), ROW(A1)))-SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1)))*65536

becomes

((SMALL(IF(tbl=TRUE, {"", 1, "", "", "";"", "", "", "", 2;3, "", "", "", "";"", 4, "", 4, 4;"", "", "", 5, 5}+({1, 2, 3, 4, 5})/65536, ""), ROW(A1)))-SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1)))*65536

becomes

((SMALL(IF(tbl=TRUE, ({"", 1, "", "", "";"", "", "", "", 2;3, "", "", "", "";"", 4, "", 4, 4;"", "", "", 5, 5}+{0.0000152587890625, 0.000030517578125, 0.0000457763671875, 0.00006103515625, 0.0000762939453125}, ""), ROW(A1)))-SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1)))*65536

becomes

(1.0,000030517578125-SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1)))*65536

becomes

(1,000030517578125)-1)*65536

becomes

(0.000030517578125)*65536

becomes

(0.0000152587890625)*65536

and returns column number 2.

**Step 3 - Convert row and column numbers into an address**

=ADDRESS(SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1)), ((SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1)+(COLUMN(tbl)-MIN(COLUMN(tbl))+1)/65536, ""), ROW(A1)))-SMALL(IF(tbl=TRUE, (ROW(tbl)-MIN(ROW(tbl))+1), ""), ROW(A1)))*65536)

becomes

=ADDRESS(1, 2) returns $B$1 in cell B7.

### Download excel file

cell addresses.xls

(Excel 97-2003 Workbook *.xls)

### Functions:

**ADDRESS(**row_num;column_num**)** creates a cell reference as text, given specified row and column numbers

**SMALL(**array,k**)** returns the k-th smallest row number in this data set.

**ROW(**reference**)** returns the rownumber of a reference

**COLUMN(**reference**)** returns the columnnumber of a reference

**RIGHT(**text;num_chars**)** returns the specified number of characters from the end of textstring

Create a list of dates with blanks between quarters in excel

Question: How do I create a list of dates with blanks between each quarter? (Q1, Q2, Q3 and Q4) Answer: […]Find positive and negative amounts that net to zero in excel

I found this excel question: I am a Controller in a multinational company. We have many transactions (sales, credits, debits, […]How to create random numbers, text strings, dates and time values

In excel the RAND() function returns a number greater than or equal to 0 (zero) and less than 1. Combining […]### 2 Responses to “Get cell addresses from several cells with array formula”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

Is it really works? I have tried this formula few times. It doesn't work. But i really some help in this topic. Please enlighten me. Thousand of thanks!

Regards,

Pari

Parimalah,

I have now updated this post.

Thank you for commenting!