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>

 

[AhoyNC]

The only thing I've been able to come up with is to add a helper column to extract the numbers from the value column.
The formula in D2 to extract numbers comes from this link.

https://www.youtube.com/watch?v=bqsvOygpQWc

Excel Workbook
	A	B	C	D	E
1	ID	Value	Date	Helper
2	12345678A	NULL	1/15/2013	0
3	12345678A	Low100a	1/25/2013	100
4	12345678A	150	1/26/2013	150
5	12345678B	200	1/27/2013	200
6	12345678B	100H2	1/30/2013	1002
7
8	12345678A	100	1/25/2013
9
Sheet

 

[maldonadocj]

Hello AhoyNC,

Your formulas worked!! Thank you very much.

Best regards,
maldonadocj

 

[AhoyNC]

You're welcome. Glad they worked for you. Thanks for the feedback.

 

[maldonadocj]

Hello AhoyNC,

Was wondering if you may assist with a similar inquiry. I am trying to solve for a numeric value (cell C6) given a text id (cell A6) and a date(cell B6). Pls. see example below. It is likely a derivation of one of the formulas you provided me previously but I have done several iterations of it to no avail.

Thank you in advance.

Table 1
	A	B	C
1	ID	Value	Date
2	12345678A	100	5/15/2014
3	12345678A	102	5/9/2014
4	12345678B	103	6/1/2014
5
6	12345678A	5/15/2014	100

<colgroup><col width="64" style="width: 48pt;"> <col width="91" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3328;"> <col width="64" style="width: 48pt;"> <col width="90" style="width: 68pt; mso-width-source: userset; mso-width-alt: 3291;"> <tbody> </tbody>

 

[AhoyNC]

Try:

An array formula that must be entered with

Excel Workbook
	A	B	C	D
1	ID	Value	Date
2	12345678A	100	5/15/2014
3	12345678A	102	5/9/2014
4	12345678B	103	6/1/2014
5
6	12345678A	5/15/2014	100
7
CTRL-SHIFT-ENTER.

 

[Momentman]

It appears this also works

Excel 2010
	A	B	C
1	ID	Value	Date
2	12345678A	100	5/15/2014
3	12345678A	102	05/09/2014
4	12345678B	103	06/01/2014
5
6	12345678A	5/15/2014	100
Sheet4

Cell Formulas
Range	Formula
C6	=SUMPRODUCT(($A$2:$A$4=$A$6)*($C$2:$C$4=$B$6)*($B$2:$B$4))

 

[Aladin Akyurek]

Try:

An array formula that must be entered with CTRL-SHIFT-ENTER.

{=INDEX($B$2:$B$4,MATCH($A$6&$B$6,$A$2:$A$4&$C$2:$C$4,0))}

Concatenation without a delimiter can be risky, in particular when numbers are involved. Try rather:

{=INDEX($B$2:$B$4,MATCH($A6,IF($C$2:$C$4=$C6,$A$2:$A$4),0))}

 

[Momentman]

It appears this also works

Excel 2010

	A	B	C
1	ID	Value	Date
2	12345678A	100	5/15/2014
3	12345678A	102	05/09/2014
4	12345678B	103	06/01/2014
5
6	12345678A	5/15/2014	100

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula C6 =SUMPRODUCT(($A$2:$A$4=$A$6)*($C$2:$C$4=$B$6)*($B$2:$B$4)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

I guess my solution would be for another question Not the one posed here

Sorry about that

 

[AhoyNC]

Aladin,
Thanks for the feedback on the formula I used. It's always good to learn better and more efficient ways to handle these issues.

 

[maldonadocj]

Hello All,

Thank you very much for your incredible insight and assistance. Unfortunately, the formulas provided did not work due to a time stamp issue. The problem is due to differing times. Using the prior example, cell B6 has a date of 5/15/2014 and a time stamp of 12:00:00 PM whereas cell C2 has a date of 5/15/2014 but a time stamp of 3:00:00 PM. Is it possible to enhance the formulas to ignore the time stamp and only use the date as the reference point?

Best regards,
maldonadocj