Find first number in column using match/index function

[maldonadocj]

Hello,
Can someone pls. assist me with providing a formula, ideally using the index/match functions, to locate the first absolute (may have positive or negative values) number in a column? I have added snapshots of how the data would look on the two sheets they are currently located on.

Thank you in advance.

Sheet 1

ID	Value
12345678A	NULL
12345678A	100
12345678A	150
12345678B	200

<colgroup><col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

Sheet2

ID	Expected Result
12345678A	100

<colgroup><col width="93" style="width: 70pt; mso-width-source: userset; mso-width-alt: 3401;"> <col width="152" style="width: 114pt; mso-width-source: userset; mso-width-alt: 5558;"> <tbody> </tbody>

 

[Momentman]

Like this maybe:

Excel 2010
	A	B
1	ID	Value
2	12345678A	NULL
3	12345678A	100
4	12345678A	150
5	12345678B	200
6
7	12345678A	100
Sheet14

Cell Formulas
Range	Formula
A7	{=INDEX(A2:A5,MATCH(TRUE,ISNUMBER(1*(B2:B5)),0))}
B7	{=INDEX(B2:B5,MATCH(TRUE,ISNUMBER(1*(B2:B5)),0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[maldonadocj]

Hello Momentman,

Thank you for the prompt response. Pls forgive my ignorance but can you pls. explain to me how the formula in cell B7 is linked to the id noted in cell A7? Pls. note that it is not necessary to have a formula to define the id in cell A7 as the ids will be defined as a user input as noted in Sheet 2 in my initial post.

Pls. advise.

Best regards,
maldonadocj

 

[AhoyNC]

Try changing formula in B7 to example below.

This is an array formula and must be entered with

Excel Workbook
	A	B	C
1	ID	Value
2	12345678A	NULL
3	12345678A	100
4	12345678A	150
5	12345678B	200
6
7	12345678A	100
8
CTRL-SHIFT-ENTER

 

[Momentman]

Hello Momentman,

Thank you for the prompt response. Pls forgive my ignorance but can you pls. explain to me how the formula in cell B7 is linked to the id noted in cell A7? Pls. note that it is not necessary to have a formula to define the id in cell A7 as the ids will be defined as a user input as noted in Sheet 2 in my initial post.

Pls. advise.

Best regards,
maldonadocj

I must have answered the wrong question, thought you needed the first number in the series irrespective of IDs.

AhoyNC's formula will do what you need , this portion handles your query (A2:A5=A7) which is what i didn't have in mine

Victor

 

[maldonadocj]

Hello AhoyNC,

Thank you very much for your assistance. The formula worked perfectly. However, I have additional nuances that would need to be added. Can you pls. provide me an additional formula that would retrieve the date associated with the numerical value that was returned from your formula? Additionally, is it possible to revise your formula so that in addition to retrieving the numerical value it will also retrieve any numerical values that were imbedded in the string? Pls. see example below:

	A	B	C
1	ID	Value	Date
2	12345678A	NULL	1/15/2013
3	12345678A	100H	1/25/2013
4	12345678A	150	1/26/2013
5	12345678A	200	1/27/2013
6	12345678B	250	9/29/2013
7
8	12345678A	100	1/25/2013

<tbody>
</tbody>

Best regards,
maldonadocj

 

[maldonadocj]

Hello Momentman,

No worries. I appreciate that you followed up. I sent a follow up post to AhoyNC regarding several new additions to the formula provided. Pls. feel free to review my post and let me know if you are also able to address the requests.

Thank you.
maldonadocj

 

[AhoyNC]

Is there any pattern to how the numbers appear in the string. Will they always be at the beginning and 3 digits as in your example?

 

[maldonadocj]

Hello AhoyNC,

Great question. There is no set pattern. The text may also be in front such as "Low100" or both "LM100a". Hope this helps.
Thank you,
maldonadocj

 

[AhoyNC]

Will the numbers always be together as in your post Low100 LM100a. Could you ever have "Low10ab45"?