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

### Category: Misc

Exact word in string without using vba in excel

I read an interesting blog post Is A Particular Word Contained In A Text String? on Spreadsheetpage. That inspired me […]Comments(25) Filed in category: Excel, Misc

Comments(13) Filed in category: Excel, Misc

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: […]Comments(6) Filed in category: Dates, Excel, Misc

Comments(5) Filed in category: Excel, Misc

Text to be on top of column in excel

Question: I have a text name coloumn, there will be only 1 value to be listed in 1 coloumn in […]Comments(4) Filed in category: Excel, Misc

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, […]Comments(4) Filed in category: Count values, Excel, Misc, Solver

Quickly select a range of values in excel

Copying or moving cells is probably one of the most common activities in excel. Here is how to quickly select […]Comments(2) Filed in category: Excel, Misc

How to enter array formulas in merged cells

Have you ever tried to enter an array formula in merged cells? Then you are familiar with this error message: […]Comments(2) Filed in category: Combine/Merge, Excel, Misc

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 […]Comments(2) Filed in category: Dates, Excel, Misc

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