Excel: Get contents of first cell in a range that has data in Excel 2003/XP/2000/97

Question: In Excel 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:

=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))