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

Answer: Use this formula. The formula is used in B9:B19.

=ADDRESS(SMALL((IF(B2:K7=TRUE,ROW(B2:K7),"")),ROW()-8), RIGHT(SMALL((IF(B2:K7=TRUE,((ROW(B2:K7))&","&(COLUMN(B2:K7)))*1,"")),ROW()-8),FIND(",", SMALL((IF(B2:K7=TRUE,((ROW(B2:K7))&","&(COLUMN(B2:K7)))*1,"")),ROW()-8))-1))  + Press Ctrl + Shift + Enter

SMALL((IF(B2:K7=TRUE,ROW(B2:K7),"")),ROW()-8) returns the row numbers of the array.

RIGHT(SMALL((IF(B2:K7=TRUE,((ROW(B2:K7))&","&(COLUMN(B2:K7)))*1,"")),ROW()-8),FIND(",", SMALL((IF(B2:K7=TRUE,((ROW(B2:K7))&","&(COLUMN(B2:K7)))*1,"")),ROW()-8))-1) returns the column numbers of the array.

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:

1. Formula for matching a date within a date range in excel
2. Filter unique distinct text values using “begins with” criterion in a range using array formula in excel
3. Sum text cells using criteria in excel
4. Group similar cell values on same row in excel
5. Sorting text cells using array formula in excel
6. Sorting numbers and text cells also removing blanks using array formula in excel
7. Sorting numbers and text cells descending also removing blanks using array formula in excel
8. Sort text cells alphabetically from two columns using excel array formula
9. Sort text values by length using array formula in excel