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.

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:


Leave a Reply