Check arrays for value.

[Robert Hatcher]

I have several arrays of unique numbers (no array has any of the same
numbers). I would like to have a formula in a cell that checks for
the presense of a value in the arrays and returns the name of the
array containing the value. The input value is in an adjacent cell.
The arrays are all named.

Simplified version of the data:

Arrays;
FS = 1,2,3
AS = 4,5,6
AP = 7,8,9
FP = 10,11,12

If the input is 5 "AS" is returned

Thanks
Robert

 

[Don Guillett]

I have several arrays of unique numbers (no array has any of the same
numbers).  I would like to have a formula in a cell that checks for
the presense of a value in the arrays and returns the name of the
array containing the value. The input value is in an adjacent cell.
The arrays are all named.

Simplified version of the data:

Arrays;
FS = 1,2,3
AS = 4,5,6
AP = 7,8,9
FP =  10,11,12

If the input is 5 "AS" is returned

Thanks
Robert

If AS in col A and number arrays in col b

Sub find5row()
For Each c In Range("b1:b21")
If InStr(c, 5) Then
MsgBox "5 found at row " & c.Row _
& " for " & Cells(c.Row, 1)
Exit For
End If
Next c
End Sub

 

[Robert Hatcher]

Thanks Don,
Because of It limitations on the site I need to do this without VBA.
Robert

 

[Claus Busch]

Hi Robert,

Am Wed, 28 Sep 2011 05:20:15 -0700 (PDT) schrieb Robert Hatcher:

Because of It limitations on the site I need to do this without VBA.

names of arrays in col A, arrays in col B, input 5 in D1:
Array formula:
=INDEX(A:A,MATCH(TRUE,FIND(D1,$B$1:$B$100)>0,0))
enter with CTRL + Shift + Enter


Regards
Claus Busch

 

[Robert Hatcher]

Thanks claus, I will try that.

Hi Robert,

Am Wed, 28 Sep 2011 05:20:15 -0700 (PDT) schrieb Robert Hatcher:


names of arrays in col A, arrays in col B, input 5 in D1:
Array formula:
=INDEX(A:A,MATCH(TRUE,FIND(D1,$B$1:$B$100)>0,0))
enter with CTRL + Shift + Enter

Regards
Claus Busch