Finding a value in an array (excel 97)

Topic

New Reply

I have a worksheet where I want to hunt out a persons name in a range of cells, then return a value from another range in the corresponding row the persons name is in.

I have an array formula that is doing the second part (returning the value in the same row) once the row and column of the persons name is known.

Where I am stuck is determining that row and column. The persons name and the range to look in are variable. I have functions (rowFind & colFind) that I was going to put into my array formula. The code works when written as macros with name and range set, but return #Value as functions with varables.

The attached worksheet will illustrate the aboe better. It contains the array formula, functions and simplified sample data.

Any pointers / explanations as to where I am going wrong will be much appreciated.

Reply | Quote

Replies

I think that the following function does it all. “Where” is the entire range including in this case the team members and the type. “What” is a string or a cell containing a string that is a team member to look for in “Where.” “Col” is a long or a cell containing a long that gives the column to to return (relative to the begining of “Where”). HTH –Sam

Option Explicit
Public Function myFind(Where As Range, What As Variant, Col As Variant) As Variant
Dim c As Range
    Set c = Where.Find(What:=What, lookat:=xlWhole)
    myFind = Where.Cells(c.Row - Where.Row + 1, Col)
End Function

Reply | Quote

Thanks for your time Sam,
The function is still returning #value! error, but it has given me another angle from which to think about the problem.

thanks

Reply | Quote

Bizzare! The function works fine in XL 2002, but gives #VALUE! in 97 and 2000. Sorry, don’t know if I have time right now to figure it out but maybe tonight. –Sam

Reply | Quote

Andrew,

The following is a VBA solution, which you might or might not be able to use.
It is not a practical solution if the range of names is several thousands rows long. (Too much time required)
A non-VBA solution would be to use the “V-Lookup” function.
Code follows:

‘——————————————————————————–
‘Finds a specific name in specified range.
‘Written by Jim Cone 11/13/2002

Function FindTheRightValue(NameToFind As String) As String
Dim oCell As Range
Dim NameRange As Range

Set NameRange = Range(“A1:C5″)
NameToFind = LCase$(NameToFind)

For Each oCell In NameRange
If LCase$(oCell.Text) = NameToFind Then
‘Assumes the look up value is always in column 4
FindTheRightValue = Cells(oCell.Row, 4).Text
Exit For
End If
Next
If Len(FindTheRightValue) Then MsgBox FindTheRightValue _
Else MsgBox NameToFind & ” was not found.”

Set oCell = Nothing
Set NameRange = Nothing
End Function

‘Call the function.
Sub WhereIsIt()
FindTheRightValue (“Mary”)
End Sub
‘————————————————————————–

Jim Cone
San Jose, CA

Reply | Quote

Thanks Jim,
Your code produces the correct result. I will tweak it to return the value in the cell instead of a msg box and set a variable for the range to look in and I should be cooking with gas.

VLookup wasn’t an option as it will only search in the leftmost column of the array and the name is not always in that column.

Thanks

Reply | Quote

Try this array formula:

=INDEX(D2:D5,MAX(IF(ISERROR(SEARCH(G1,A2:A5&B2:B5)),””,ROW(1:4))))

Reply | Quote

Turns out that you cannot use Find in a User-Defined Function until Xl 2002, so I modified my function to a for each loop. Also attached a worksheet with both the function and Jan Karl’s formula (Jan’s still needs a little help when a name is not found). HTH –Sam

Option Explicit

Public Function myFind(Where As Range, What As Variant, _
                        Col As Variant) As Variant
Dim c As Range
    myFind = "#N/A"
    For Each c In Where
        If c = What Then _
            myFind = Where.Cells(c.Row - Where.Row + 1, Col)
    Next c
End Function

Reply | Quote

Thank you Sam for your refined code. It is working a treat.
An Interesting aside, if the ‘Where’ & ‘What’ is in another workbook it returns #value! unless the source workbook is open. Jim Cone’s code is the same. I vaguely remember something about this in relation to user defined functions from somewhere.
Anyway thanks again for the help, Jim and Jan also.

Reply | Quote

Sam, would it better if you used:

myFind = CVErr(xlErrNA)

since this will permit use if ISERROR and ISNA functions on the return?

Reply | Quote

Yep, but I didn’t remember how to do it!

Reply | Quote

Not pretty, but this works:

=INDEX($D$2:$D$5,IF(MAX(IF(ISERROR(SEARCH(G7,$A$2:$A$5&$B$2:$B$5)),””,ROW($1:$4)))=0,99999,MAX(IF(ISERROR(SEARCH(G7,$A$2:$A$5&$B$2:$B$5)),””,ROW($1:$4)))))

Reply | Quote

In H1 enter:

=INDEX($D$2:$D$5,MATCH(G1,INDEX($A$2:$C$5,0,SUMPRODUCT(($A$2:$C$5=G1)*(COLUMN($A$2:$C$5)))),0))

Caveat. If G1 is not unique in the range of interest, the SumProduct part needs to be modified to take that into account.

Aladin

Reply | Quote

Solved beautifully!

Reply | Quote