Find all lookup values and return address

[jomili]

In this article (https://excelribbon.tips.net/T010083...F4X&dn=2)there are two functions, both called "FindMe". The second one, by Peter Atherton, at the bottom of this post, I renamed FindAllMes, as the purpose of it is to return the address of all Lookup values in your range. You use it like

Please Login or Register  to view this content.

It works, but has one issue, and there's one thing I'd like to add, but don't know how.
The issue is that it returns the Address twice. For instance, I tried it on two values that exist at:
A28, A29, A52, and A53.
However the function returned this:
$A$28, $A$28, $A$29, $A$29, $A$52, $A$52, $A$53, $A$53.
I'd like to know how to fix that.

What I'd like to Add: Right now the function requires a typed string, like "Fred,Apple". I'd like to be able to put in a range reference, like F23:F24. Trying that with the function as it is produces a VALUE error.

The article is from 2016 so I didn't see any use in commenting there, and I'm hoping someone here can help me get this guy running good. I appreciate any help I can get.

Please Login or Register  to view this content.

[romperstomper]

Based on the code you posted, that would have to be called using:

=FindAllMes(A2:A11,"Fred,Apple")

and not:

=FindAllMes("Fred,Apple",A2:A11)

It would only return the address twice if both items were found in the same cell.

[jomili]

You're right, I had it backwards (due to trying to resolve the issue). See the attached, with doubled results.

[romperstomper]

As I said, you get the double addresses because both values are in the same cell - your function is coded to use a comma as a separator and your lookup values contain commas, so it matches twice. You should really use something else as the separator, such as a pipe symbol or ampersand.

If you add Exit For after this line:

Please Login or Register  to view this content.

you shouldn't get the same address repeated for any reason.

[jomili]

Thanks for pointing out the comma problem; I never would have spotted that. I've changed the macro as shown below, and now it's working, just need to remember to use the pipe symbol to separate my lookup values. I also can now use the Cell range like this:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[romperstomper]

You can also do this:

Please Login or Register  to view this content.

then use:

=FindAllMes(A3:A46,F3:F4)

[jomili]

Whoops, missed the "Exit For" comment. Doing that, I can go back to my comma, like this:

Please Login or Register  to view this content.

Thanks so much!

EDIT: Sorry, posted this before I saw your last post.