Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Hamilton, Waikato, New Zealand
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Finding a value in an array (excel 97)

    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.
    Attached Files Attached Files

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding a value in an array (excel 97)

    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
    <pre>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</pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  3. #3
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Finding a value in an array (excel 97)

    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

  4. #4
    Lounger
    Join Date
    Jan 2001
    Location
    Hamilton, Waikato, New Zealand
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a value in an array (excel 97)

    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

  5. #5
    Lounger
    Join Date
    Jan 2001
    Location
    Hamilton, Waikato, New Zealand
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a value in an array (excel 97)

    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

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a value in an array (excel 97)

    Try this array formula:

    =INDEX(D25,MAX(IF(ISERROR(SEARCH(G1,A2:A5&B2:B5)),"",ROW(1: 4))))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding a value in an array (excel 97)

    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
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding a value in an array (excel 97)

    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
    <pre>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</pre>

    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    Lounger
    Join Date
    Jan 2001
    Location
    Hamilton, Waikato, New Zealand
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a value in an array (excel 97)

    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.

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Finding a value in an array (excel 97)

    Sam, would it better if you used:

    myFind = CVErr(xlErrNA)

    since this will permit use if ISERROR and ISNA functions on the return?
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Finding a value in an array (excel 97)

    Yep, but I didn't remember how to do it! <img src=/S/newbrain.gif border=0 alt=newbrain width=21 height=22>
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  12. #12
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a value in an array (excel 97)

    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(IS ERROR(SEARCH(G7,$A$2:$A$5&$B$2:$B$5)),"",ROW($1:$4 )))))
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  13. #13
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a value in an array (excel 97)

    In H1 enter:

    =INDEX($D$2:$D$5,MATCH(G1,INDEX($A$2:$C$5,0,SUMPRO DUCT(($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
    Microsoft MVP - Excel

  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Finding a value in an array (excel 97)

    Solved beautifully!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •