Results 1 to 9 of 9

Thread: formula (2002)

  1. #1
    New Lounger
    Join Date
    Jun 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formula (2002)

    Good day. I need to discover whether an entry in a given cell is also in any of 4 other columns of similar data (street names, in this case). I've assigned the 4 columns a group name (an array?) and used it in a formula as follows: VLOOKUP(A2, TNowAry, 1,0). As stated, the formula tries to determine whether the street name in A2 is also in 4 other columns of street names that I've defined as "TNowAry." The result is "NA" but when I look at the fields, I see some names duplicated in all 4 columns. Failure. Thanks for any assistance. Happy Holidays. w

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula (2002)

    VLOOKUP only looks for a match in the first column of the table_array (the second argument). None of the Excel search functions will do what you want, as far as I know. You could use the following custom function:

    Public Function IsFound(AValue As Variant, ARange As Range) As Boolean
    Dim rng As Range
    Set rng = ARange.Find(What:=AValue, LookIn:=xlValues, LookAt:=xlWhole)
    IsFound = Not (rng Is Nothing)
    End Function

    Use like this:

    =IsFound(A2,TNowAry)

  3. #3
    New Lounger
    Join Date
    Jun 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula (2002)

    Thank you very much for your speedy reply. I've pasted the code into a Module Window and Inserted a Custom Function. The results are True or False but not reflective of what I see. If you'd care to see a sample, I've attached a worksheet. Thanks very much. w
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula (2002)

    Please explain why the formula doesn't return what you want. "Baltimore St" is the only item that doesn't occur in any of the 4 columns, so the function returns FALSE in row 3. The other items do occur, so the function returns TRUE in the other rows.

  5. #5
    New Lounger
    Join Date
    Jun 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula (2002)

    I apologize for the confusion, sir. I need to know which address occurs in all fields (years), if any. I'm sorry for not making that clear at the outset. Thank you. w

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula (2002)

    Sorry, I don't understand what you mean by the addition "if any". Please try to explain clearly and exactly what you want.

  7. #7
    New Lounger
    Join Date
    Jun 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula (2002)

    I'm sorry. I need to know whether any address occurs 5 times, i.e., in all five fields, including the first field. Thank you for your patience, sir.
    w

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: formula (2002)

    You can use the following formula. If you want to use named ranges, you'll have to define a name for each of the four lookup columns.

    =NOT(OR(ISERROR(MATCH(P2,$BM$2:$BM$199,0)),ISERROR (MATCH(P2,$DJ$2:$DJ$199,0)),ISERROR(MATCH(P2,$FG$2 :$FG$199,0)),ISERROR(MATCH(P2,$HD$2:$HD$199,0))))

    This formula doesn't use a custom VBA function, only built-in functions.

  9. #9
    New Lounger
    Join Date
    Jun 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formula (2002)

    That's it!! Prof. Hans, you've don it again! My appreciation is inexpressible (like my explanations). Thank you very much! Best wishes for a Happy New Year!!

Posting Permissions

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