Search Against Array w/ Wildcards and Secondary Output Column?

I may not be explaining this well, but here's what I'm working with:

2m1j7XX.jpg

And what I need to do is, for every cell in the Entry column, search through the Token column for a match (obviously wildcards will be required), and if one is found, spit out the corresponding cell in the Output column. I've spent the last 30 minutes looking for a proper solution. It's probably something simple, but I can't get INDIRECT/SEARCH to work with both an array and wildcards to compensate for not having direct matches to work with.

Let me know if that isn't clear, my find product would be the Entry columns with the appropriate values from the Output column, based on what the search found (if anything) in the Token column.

Hi an_otter,

Try something like this:

Please Login or Register  to view this content.

The 'like' commands uses 'regular expresssions' with a twist. In the debugger, double click on 'like', and then press 'f1' to get more information about the 'like' command.

Lewis

Originally Posted by newdoverman

This is possible:

	A	B	C	D
1	Token	Output		Entry
2	Cat	ABC CAT XYZ	=VLOOKUP("*"&A2&"*",$D$2:$D$4,1,0)	ABC DOG XYZ
3	Dog	ABC DOG XYZ		ABC BIRD XYZ
4	Bird	ABC BIRD XYZ		ABC CAT XYZ

This returns the value in column D. If you want another response, wrap the formula in an IF statement like this:

Formula:

Please Login or Register  to view this content.

Substitute the text with what you want.

I know this seems like the logical approach, I did the same, but I need this in reverse. So the C column in your example needs to spit out the B values associated with the A range, which is parsed through for each entry in D. If that makes sense. I'd need to parse through column A for each entry in column D to see if there's a match, and if there is, the output needs to be whatever's in column B.

That doesn't exactly fit my need, since I'm not looking to see if each entry in list A exists in list D, I'm looking to see if each entry in list D contain any entry in list A, and then provide the neighbouring entry in list B if found.

As an example of what I'd need to provide the output I require (this doesn't work):

Formula:

Please Login or Register  to view this content.

And so on. So I need to search the entire column A for a value that may exist in each cell of column D, and if one is found, the output is the entry next to it in column B.

I apologize if I'm not making this clear, Google doesn't get what I need, either.