Thanks:  0
Likes:  0

# Thread: Find first number in column using match/index function

1. ## Find first number in column using match/index function

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.

Sheet 1

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

Sheet2

 ID Expected Result 12345678A 100

2. ## Re: Find first number in column using match/index function

Like this maybe:

Excel 2010
AB
1IDValue
212345678ANULL
312345678A100
412345678A150
512345678B200
6
712345678A100

Sheet14

Array Formulas
CellFormula
A7{=INDEX(A2:A5,MATCH(TRUE,ISNUMBER(1*(B2:B5)),0))}
B7{=INDEX(B2:B5,MATCH(TRUE,ISNUMBER(1*(B2:B5)),0))}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

3. ## Re: Find first number in column using match/index function

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.

Best regards,

4. ## Re: Find first number in column using match/index function

Try changing formula in B7 to example below.

This is an array formula and must be entered with CTRL-SHIFT-ENTER

 A B C 1 ID Value 2 12345678A NULL 3 12345678A 100 4 12345678A 150 5 12345678B 200 6 7 12345678A 100 8

 Cell Formula B7 {=INDEX(B2:B5,MATCH(1,ISNUMBER(1*(B2:B5))*(A2:A5=A7),0))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

5. ## Re: Find first number in column using match/index function

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.

Best regards,
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

6. ## Re: Find first number in column using match/index function

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

Best regards,

7. ## Re: Find first number in column using match/index function

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.

8. ## Re: Find first number in column using match/index function

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?

9. ## Re: Find first number in column using match/index function

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,

10. ## Re: Find first number in column using match/index function

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•