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

On Sep 27, 9:08*am, Robert Hatcher <rhatcher...@gmail.com> wrote:
> 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

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

On Sep 27, 6:48*pm, Don Guillett <dguille...@gmail.com> wrote:
> On Sep 27, 9:08*am, Robert Hatcher <rhatcher...@gmail.com> wrote:
>
>
>
> > 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- Hide quoted text -
>
> - Show quoted text -

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Thanks claus, I will try that.

On Sep 28, 8:50*am, Claus Busch <claus_bu...@t-online.de> wrote:
> 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
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2