Check arrays for value.

  • Thread starter Thread starter Robert Hatcher
  • Start date Start date
R

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
 
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
Click to expand...
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
 
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.
Click to expand...

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
 
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
Click to expand...
 
Back
Top