---

MS Excel: Get contents of first cell in a range that has data

This Excel tutorial explains how to get the contents of the first cell in a range that has data (with screenshots and step-by-step instructions).

Question: In Microsoft Excel 2010/2007/2003/XP/2000/97, how do I make a cell display the contents of the first cell of a range that has data?

Answer: This is a bit tricky and the formula that you need to use will depend on the types of data that you have in the range.

Data Range is Formatted as Text

If your range contains only text values, you could use the following formula that uses the INDEX and MATCH functions:

=INDEX(range,MATCH("*",range,0))

where range is the range that you wish to check

For example, if you wanted to find the first text value in column A, you would use the following formula:

=INDEX(A:A,MATCH("*",A:A,0))

For example, if you wanted to find the first text value in range C2:C10, you would use the following formula:

=INDEX(C2:C10,MATCH("*",C2:C10,0))