find first numeric value in range

[nicolewh]

Hope everyone had a wonderful Thanksgiving!!

I have a range A1 to A7 which includes text, empty cells, and numbers. I am trying to find the function to return the first instance of a number in the range. so for example (from A1 to A7) we have in each cell respectively:
"Orange", "", "", "12:35 PM", "", "", "1:15 PM"
The formula would return 12:35 PM

Thanks so much!

Nicole

 

[sanrv1f]

try this

Excel Workbook
	A	B	C
1	san
2
3
4	12:35		12:35
Sheet4

Excel 2003

Cell Formulas
Range	Formula
C4	=INDEX(A1:A4,MATCH(TRUE,INDEX(ISNUMBER(A1:A4),0),0))

 

[nicolewh]

Thanks!

Nicole

 

[nicolewh]

On those same lines - how would I integrate a vlookup function into this formula, returning a corresponding value in another column.

So in our example, wherever the numeric value first appears in column A return the value from that same row but this time from column B. If cell B4 was "Apple" it would return "Apple" instead of "12:35"

Thanks

Nicole

 

[pgc01]

Hi Nicole

You just have to change the range reference in Sankar's formula:

=INDEX(B1:B4,MATCH(TRUE,INDEX(ISNUMBER(A1:A4),0),0))

 

[nicolewh]

Awesome! That did it.

Nicole

 

[snets123]

try this

Excel Workbook
	A	B	C
1	san
2
3
4	12:35		12:35
Sheet4

Excel 2003

Cell Formulas
Range	Formula
C4	=INDEX(A1:A4,MATCH(TRUE,INDEX(ISNUMBER(A1:A4),0),0))

Would anyone know if there is a way to than find the 2nd and 3rd following numeric values after this?

 

[Eric W]

As you have Excel 365, it's much easier than in the old days. Use the FILTER function:

Book2
	A	B	C	D	E	F
1	san		0.524306		n	2
2			42			42
3			37
4	12:35		21
5	horse
6
7	42
8
9	banana
10	37
11	21
12
Sheet12

Cell Formulas
Range	Formula
C1:C4	C1	=FILTER(A1:A12,ISNUMBER(A1:A12))
F2	F2	=INDEX(FILTER(A1:A20,ISNUMBER(A1:A20)),F1)
Dynamic array formulas.

The C1 formula lists out all the numeric values. The F2 formula lets you put the value you want in F1.

Incidentally, it's better to open a new thread with a question than to add your question to the bottom of a 12-year old thread. It's very possible no one will ever see your question.