Lookup based on matrix values

[andy62]

I am struggling to develop one of those "copy down" functions that can
produce a set of values. In this simplified example, the formula needs to
tell me the name from column A if there is a value in the remaining array
(B15).

Dave 1 2 3
Bob 1
Sarah 3
George 2
Sally 1

So the first instance of the formula would produce "Dave", the second would
produce "Bob", the third ""George", the fourth is "Dave" again, followed by
"Sarah", etc. Yes, I need it to read the array down the first column before
it moves on to the next column.

For what it's worth, here's what is not working for me:
=INDEX($B$15:$B$68,SMALL(IF($C$15:$Z$68<>"",ROW($C$15:$N$68)-14),ROWS($B$70:H70))).
It's an INDEX function using the SMALL function to produce sequential values
and the IF function to identify all the places where the array is populated.
It's entered as an array function, of course.

Any ideas? TIA

 

[BSc Chem Eng Rick]

Andy,

This is no simple task and is easiest achieved by wrtting a custom function.
Open your VB editor (Alt+F11). Insert a Module in your workbook. Copy the
below function into the module. Now go back to your worksheet. You will see
you can insert the NAMEFINDER function. The first argument is the column of
names. the second argument is the ENTIRE number matrix excluding the names
column. The function returns a COLUMN array so you must execute with
Ctrl+Shift+Enter. I know this is not the copy down formula you were looking
for but you need only extend the number of selected cells before you execute
to achieve a similar result.

Function NAMEFINDER(ByVal Names_Range As Range, ByVal Matrix As Range) As
Variant
Dim Answer() As String
Dim TheRows As Long, TheColumns As Long
Dim i As Long, j As Long, z As Long
z = 0
TheRows = Matrix.Rows.Count
TheColumns = Matrix.Columns.Count
For j = 1 To TheColumns
For i = 1 To TheRows
If Matrix.Cells(i, j).Value <> "" Then
ReDim Preserve Answer(z)
Answer(z) = Names_Range(i).Value
z = z + 1
End If
Next i
Next j
NAMEFINDER = Application.WorksheetFunction.Transpose(Answer)
End Function

 

[BSc Chem Eng Rick]

Andy,

What you are trying to do is not simple and is best achieved by writing a
custom function in VB. Open VB editor (Alt+F11). Insert a Module and copy the
below code into the module. Now go back to your spreadsheet and you will see
you can insert the NAMEFINDER function. The first argument is the column of
names you have, the second is the ENTIRE matrix. The function returns a
COLUMN array so you must select a vertical range, type the formula and
execute with Ctrl+Shift+Enter. I know this is not the copy down result you
needed but you can simply extend the number of selected cells before you
execute the formula to achieve a similar effect.

Function NAMEFINDER(ByVal Names_Range As Range, ByVal Matrix As Range) As
Variant
Dim Answer() As String
Dim TheRows As Long, TheColumns As Long
Dim i As Long, j As Long, z As Long
z = 0
TheRows = Matrix.Rows.Count
TheColumns = Matrix.Columns.Count
For j = 1 To TheColumns
For i = 1 To TheRows
If Matrix.Cells(i, j).Value <> "" Then
ReDim Preserve Answer(z)
Answer(z) = Names_Range(i).Value
z = z + 1
End If
Next i
Next j
NAMEFINDER = Application.WorksheetFunction.Transpose(Answer)
End Function