Find first number in column using match/index function

maldonadocj

Board Regular
Joined
Oct 19, 2004
Messages
103
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

IDValue
12345678ANULL
12345678A100
12345678A150
12345678B200
<colgroup><col width="118" style="width: 89pt; mso-width-source: userset; mso-width-alt: 4315;"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

Sheet2

IDExpected Result
12345678A100
<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>
 
See the change made to the index / match formula in cell 6. This is an array formula and must be entered with CTRL-SHIFT-ENTER.
The formula in C7 is an update to Momentman's sumproduct formula.
Excel Workbook
ABCD
1IDValueDate
212345678A1005/15/14 3:00 PM
312345678A1025/9/14 12:00 AM
412345678B1036/1/14 12:00 AM
5
612345678A5/15/14 12:00 PM100
7100
8
Sheet
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Hello AhoyNC,

Apologies in advance for re-visiting this thread. I have come across another issue that your formulas could not address. It appears that when there is a blank space in addition to text in a column, the formula noted below is unable to retrieve the numerical value. Can you pls. revise the formula such that it is able to skip over blanks and text in a column in order to retrieve the first numerical value? I appreciate your continued assistance in this matter.

ABC
1ID ValueDate
212345678ANULL6/15/2014
312345678A6/15/2014
412345678A1006/15/2014
5123456BB26/14/2014
6123456BB22006/14/2014
7
812345678A1006/15/2014
9

<tbody>
</tbody>

Cell Formula
B8{=INDEX(B2:B6,MATCH(1,(B2:B6>0)*(A2:A6=A8),0))}

<tbody>
</tbody>

Best regards,
maldonadocj
 
Upvote 0
Try
Excel Workbook
ABCD
1IDValueDate
212345678ANULL6/15/2014
312345678A6/15/2014
412345678A1006/15/2014
5123456BB26/14/2014
6123456BB22006/14/2014
7
8
912345678A1006/15/2014
10
Sheet
 
Upvote 0
Hello AhoyNC,

Thank you for the prompt response. Unfortunately, your formula did not work as the output was "#n/a". I revised the formula to {=INDEX($B$2:$B$6,MATCH(1,(ISNUMBER(1*(B2:B6))*($A$2:$A$6=$A$9),0))} but still received an incorrect output of "0.00". Pls. note that I removed the reference to the dates "(INT(C2:C6)=INT(C9)" in the your and my revised formulas as it is not needed. I also formatted the entire worksheet to a "General" format and then changed the "Value" column to the "Number" format but neither approach worked. Pls. advise.

Best regards,
maldonadocj
 
Upvote 0
Hello AhoyNC,

Thank you for the prompt response. Unfortunately, your formula did not work as the output was "#n/a". I revised the formula to {=INDEX($B$2:$B$6,MATCH(1,(ISNUMBER(1*(B2:B6))*($A$2:$A$6=$A$9),0))} but still received an incorrect output of "0.00". Pls. note that I removed the reference to the dates "(INT(C2:C6)=INT(C9)" in the your and my revised formulas as it is not needed. I also formatted the entire worksheet to a "General" format and then changed the "Value" column to the "Number" format but neither approach worked. Pls. advise.

Best regards,
maldonadocj

Does the following work for you?

{=INDEX($B$2:$B$6,MATCH(1,IF(ISNUMBER(1/$B$2:$B$6),IF($A$2:$A$6=$A$9,1)),0))}
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top