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

Share and Enjoy:
  • Share/Bookmark

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. Sort text values by length using array formula in excel