Article updated on March 14, 2009

Question: How to extract coordinates of not empty cells of a range (A1:D4)?



Type =SMALL(IF(A1:D4<>"",VALUE(ROW(A1:D4)&"."&COLUMN(A1:D4)),""),ROW()-10) + Ctrl + Shift + Enter in cell B11:B17.

Depending on regional settings you might have to change this part "." to "," in the above formula.

The first part of the above formula: IF(A1:D4<>"",VALUE(ROW(A1:D4)&"."&COLUMN(A1:D4)),"") gives us:


Finally =SMALL(IF(A1:D4<>"",VALUE(ROW(A1:D4)&"."&COLUMN(A1:D4)),""),ROW()-10)

Returns the k:th smallest value in array.

Download excel sample file for this tutorial.

(Excel 97-2003 Workbook *.xls)

VALUE(text) converts a text string that represents a number to a number

SMALL(array, k) returns the k-th smallest number in this data set.

returns the row number of a reference

returns the column number of a reference