Results 1 to 9 of 9
Thread: formula (2002)

20071228, 22:14 #1
 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

20071228, 22:37 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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)

20071228, 23:38 #3
 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

20071229, 00:09 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20071229, 00:25 #5
 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

20071229, 00:27 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20071229, 01:03 #7
 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

20071229, 01:15 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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 builtin functions.

20071229, 22:34 #9
 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!!