Reverse my current Match function

Hey guys and gals,

So I have two ranges of data. I would like to search one range (the long text string) for the numbers listed (OAN, column B), and then if they exist in the long text string, return the row number of the long text string they exist in (and eventually the actual substring itself).

The function I currently have does the opposite. I have attached a picture and the current code.

Please Login or Register  to view this content.

A picture is not much use, as many contributors cannot view .png files on this forum due to software incompatibilities (and besides, they can't be edited to try out different formulae). Attach a sample Excel workbook instead (the FAQ describes how to).

Pete

Try

=LOOKUP(2^15,SEARCH($B$2:$B$12,A19),$B$2:$B$12)

Notes:
There cannot be any blanks in B2:B12
If there are multiple occurrences of values from B2:B12 within A19, it will return the one that appears last in B2:B12

Thanks for the reply!

But that formula returns the string that I am looking for, rather than the row number of the string it was found in (A19:A24)

Isn't that what you wanted?

Originally Posted by sdp1019

(and eventually the actual substring itself).

Formula in column E will give the row #s.

But if the ultimate goal is the substrings as you put them in C2:C12
Then the formula in column F will do that without the use of those row #s.

SampleExcelForum-3.xlsx

Thank you so much Jonmo1!

Incredible formula by the way, I am trying to wrap my head around it right now and it is quite the task.

One more question for you, how would I modify the function to find other bits and pieces of the string? ie DATE OF LOSS, REPORT DATE, LOSS STATE, etc

You're welcome.

This is the meat of the formula
LOOKUP(2,1/ISNUMBER(SEARCH(B2,$A$19:$A$24)),$A$19:$A$24)

That returns the whole string from A19:A24 based on matching the value from B.

The rest is just a bunch of string manipulation using replace, left, Trim and Substitute.

The 51 was hardcoded based on the string always beginning with the same 51 character string.

If you want to get all the substrings...

I would recommend putting the basic formula (the lookup I showed in last post) in a column.
Copy that column, and paste as values.

Then do Data - Text To Columns
Deliminated - Next
Other - *, check "treat consecutive delimiters as one" - Finish.