Super tricky lookup problem

Hello Excel people,

I have an interesting problem I am trying to solve: We are searching for specific strings of text that are amongst lots of other characters (noise) as in the example below.

whj32Johndddejrj

The main problem is that the strings we are looking to locate in the noise are defined by a reference table (lookup table). Vlookup is not finding it easy to search for the data and is returning the largest value which is of no use.

Find attached the actual example of what I am working with.

Your thoughts on this are appreciated!

Niceguy21

In addition to this the the lookup table is not going to be in matching sequential order to the ad name column. therefore simple workarounds like using the find function on a cell by cell basis won't work: I.E it has to lookup the entire table.

Not sure how big your lookup list is but please see if you can adapt the attached

Dave H

Originally Posted by Dave H9

Not sure how big your lookup list is but please see if you can adapt the attached

Dave H

This looks promising.. just getting my head around some of these functions now. we are likely amending 50 new lookup values per week..

Originally Posted by Dave H9

Not sure how big your lookup list is but please see if you can adapt the attached

Dave H

Yes, an iterative approach using composite IF functions.

Unfortunately, with all the additional Lookups we will adding, this could get very awkward. Is there anyway to automate the process such that it
will continually cross check down the entire lookup table until a match is found, and if non are found the the process stops?

Your thoughts thanks

Ok found this here

See what you think!

Dave H

Originally Posted by Dave H9

Ok found this here

See what you think!

Dave H

#

Many thanks for this! this formula has indeed worked.. this problem has introduced me to many advanced techniques that will require further study... in particular array functions.

Originally Posted by Dave H9

Ok found this here

See what you think!

Dave H

Hi again Dave,

One question that I have is regarding the range A8:A10.. the problem is that empty cells cause the function to output a N/A values across every row.

is there some way to modify the equation to work with empty rows in lookup table? This way we can predefine the range A8:A8000 and we wont have to keep updating the range size.

Your thoughts,

Regards!

Not sure how to include this in the formula but can solve with a helper column (attached)

Hope this solves your problem.

Dave H