Return Address of a Cell Based on a String in an Array?

[genzu]

Given the following data:

A1: Random Text
A2:
A3: 32404899. 1.283362E+11 0.00025250 0.00284740 11.27683818
A4: 32433200. 1.251442E+11 0.00025917 0.00290908 11.22474968
A5: 32460185. 1.221073E+11 0.00026583 0.00297122 11.17700636
A6: 32486675. 1.192172E+11 0.00027250 0.00303356 11.13234580
A7: 32512893. 1.164641E+11 0.00027917 0.00309605 11.09031737
A8: 32538834. 1.138385E+11 0.00028583 0.00315867 11.05074584
A9: 32564470. 1.113315E+11 0.00029250 0.00322144 11.01346672
etc. for a few hundred more cells... (There is nothing in the second column,
these are just long strings)

I'm trying to come up with a formula that will Return the first
cell which has 0.003 in it (e.g. $A$6), without any intermediate formulas.

I know that 0.003 will always be in the 4th "column" of data, but the length
of the string can vary. Using a nice function I found, called "findn"
I figured out how to extract "0.003" from the 4th column

=MID(TRIM(A6),FindN(" ",TRIM(A6),3)+1,5)
(FindN finds the Nth occurrence of a the space " " within the string)

Where, in Visual Basic:

Function FindN(sFindWhat As String, _
sInputString As String, N As Integer) As Integer
Dim J As Integer

Application.Volatile
FindN = 0
For J = 1 To N
FindN = InStr(FindN + 1, sInputString, sFindWhat)
If FindN = 0 Then Exit For
Next
End Function


So I thing I'm part of the way there, if I put the above formula in column B,
and use MATCH command with ADDRESS, it gives me the cell of the
LAST one:

=ADDRESS(MATCH("0.003",B:B),1)

e.g. $A$9.

Excel Masters, is there a way to extract $A$6 from the above data without
using any intermediate formulas?? I sincerely appreciate any help!!

 

[T. Valko]

Given the following data:

A1: Random Text
A2:
A3: 32404899. 1.283362E+11 0.00025250 0.00284740 11.27683818
A4: 32433200. 1.251442E+11 0.00025917 0.00290908 11.22474968
A5: 32460185. 1.221073E+11 0.00026583 0.00297122 11.17700636
A6: 32486675. 1.192172E+11 0.00027250 0.00303356 11.13234580
A7: 32512893. 1.164641E+11 0.00027917 0.00309605 11.09031737
A8: 32538834. 1.138385E+11 0.00028583 0.00315867 11.05074584
A9: 32564470. 1.113315E+11 0.00029250 0.00322144 11.01346672
etc. for a few hundred more cells... (There is nothing in the second column,
these are just long strings)

I'm trying to come up with a formula that will Return the first
cell which has 0.003 in it (e.g. $A$6), without any intermediate formulas.

I know that 0.003 will always be in the 4th "column" of data, but the length
of the string can vary. Using a nice function I found, called "findn"
I figured out how to extract "0.003" from the 4th column

=MID(TRIM(A6),FindN(" ",TRIM(A6),3)+1,5)
(FindN finds the Nth occurrence of a the space " " within the string)

Where, in Visual Basic:

Function FindN(sFindWhat As String, _
sInputString As String, N As Integer) As Integer
Dim J As Integer

Application.Volatile
FindN = 0
For J = 1 To N
FindN = InStr(FindN + 1, sInputString, sFindWhat)
If FindN = 0 Then Exit For
Next
End Function


So I thing I'm part of the way there, if I put the above formula in column B,
and use MATCH command with ADDRESS, it gives me the cell of the
LAST one:

=ADDRESS(MATCH("0.003",B:B),1)

e.g. $A$9.

Excel Masters, is there a way to extract $A$6 from the above data without
using any intermediate formulas?? I sincerely appreciate any help!!

Try this...

=ADDRESS(MATCH("* 0.003*",A:A,0),1)

 

[genzu]

Man oh man that was too simple! Thanks VERY much. I tried to figure that
out for at least two hours . I can't believe I was making it soo difficult!

As a follow up to this, how can one write a formula to return the address of
the second occurrence, e.g.

A1: Blah
A2: asdas
A3: adasdasd
A4: skkkkj
A5: Blah
A6: asdlalklk

Formula that would return $A$5 when looking for the 2nd Blah?? I've seen
lots of array formulas with "SMALL" in them but I've yet to find one that
applies to this situation. I'm using '07 and again any help is sincerely
appreciated!!

 

[T. Valko]

Man oh man that was too simple! Thanks VERY much. I tried to figure that
out for at least two hours . I can't believe I was making it soo difficult!

As a follow up to this, how can one write a formula to return the address of
the second occurrence, e.g.

A1: Blah
A2: asdas
A3: adasdasd
A4: skkkkj
A5: Blah
A6: asdlalklk

Formula that would return $A$5 when looking for the 2nd Blah?? I've seen
lots of array formulas with "SMALL" in them but I've yet to find one that
applies to this situation. I'm using '07 and again any help is sincerely
appreciated!!

Try this...

Book1
	A	B	C	D
1	Blah	_	Blah	$A$5
2	asdas	_	_	_
3	adasdasd	_	_	_
4	skkkkj	_	_	_
5	Blah	_	_	_
6	asdlalklk	_	_	_
7	_	_	_	_
8	_	_	_	_
9	_	_	_	_
10	_	_	_	_
Sheet1

This array formula** entered in D1:

=ADDRESS(SMALL(IF(A1:A10=C1,ROW(A1:A10)),2),1)

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Haseeb Avarakkan]

Another way with just Enter,

=ADDRESS(MATCH(C1,A:A,0)+MATCH(C1,INDEX(A:A,MATCH(C1,A:A,0)+1):INDEX(A:A,65536),0),1)

 

[genzu]

Thanks again to both of you guys! Figuring out some of these formulas can
really be taxing on the brain (especially an abused one such as mine ).

Both formulas works great for me, but I'm trying to figure out a special
case where I'm only "matching" the first part of the cell, and finding the
Nth case (in this one the 3rd):

A1: Blah11asd
A2: asdas
A3: adasdasd
A4: skkkkj
A5: Blah22fff
A6: asdlalklk
A7: Blah33hih

What magic formula would return $A$7 using "Blah" as the criterion for
looking up? I can get Haseeb's formula to find the 2nd occurrence, and
Biff's formula to find the exact match of any case, but I'm looking for
one that will find the Nth case of a "partial string" and having very little
luck doing so. I though I was good at Excel, but these are tricky
formulas!!! Thanks guys!!!

 

[T. Valko]

Thanks again to both of you guys! Figuring out some of these formulas can
really be taxing on the brain (especially an abused one such as mine ).

Both formulas works great for me, but I'm trying to figure out a special
case where I'm only "matching" the first part of the cell, and finding the
Nth case (in this one the 3rd):

A1: Blah11asd
A2: asdas
A3: adasdasd
A4: skkkkj
A5: Blah22fff
A6: asdlalklk
A7: Blah33hih

What magic formula would return $A$7 using "Blah" as the criterion for
looking up? I can get Haseeb's formula to find the 2nd occurrence, and
Biff's formula to find the exact match of any case, but I'm looking for
one that will find the Nth case of a "partial string" and having very little
luck doing so. I though I was good at Excel, but these are tricky
formulas!!! Thanks guys!!!

If the lookup string is always at the beginning of the cell entry as is presented...

Array entered**:

=ADDRESS(SMALL(IF(LEFT(A1:A10,4)=C1,ROW(A1:A10)),n),1)

Where n = the nth instance you want to look for.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.