Question:

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

Array formula in cell B7:

=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)

How to create an array formula

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

Named range

tbl A1:E5

How to create a named range

  1. Select cell range A1:E5
  2. 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

Related posts:

Excel array formula: Enter a value in a cell and instantly populate adjacent cells

Sorting text cells using array formula in excel

Text to columns: Split words in a cell (excel array formula)

Sorting numbers and text cells also removing blanks using array formula in excel

Sort text cells alphabetically from two columns using excel array formula