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:
- Sorting numbers and text cells also removing blanks using array formula in excel
- Sorting numbers and text cells descending also removing blanks using array formula in excel
- Sorting text cells using array formula in excel
- Sort text cells alphabetically from two columns using excel array formula
- How to extract email addresses from a excel sheet
- Using array formula to look up multiple values in a list
- merge two sheets with array formula
- Unique values from multiple columns using array formulas
- Split values equally into groups using excel array formula
- Count number of times a string exist in multiple cells using excel formula



Leave a Reply