1. ## 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. ## 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. ## 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

4. ## 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. ## 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. ## 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. ## 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. ## 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. ## 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
•