Reverse my current Match function

[sdp1019]

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.

[Pete_UK]

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

[sonic1019]

For some reason my account was not allowing me to reply to the thread. I have created a new account to post the sample workbook.

[Jonmo1]

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

[sonic1019]

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)

[Jonmo1]

Isn't that what you wanted?

(and eventually the actual substring itself).

[sonic1019]

Not that substring, first I want to get the row number of A19:A24 that matches my B2:B12 criteria. Then I will pull out the "RISK CLAIM #" and put that next to each value B2:B12.

It would look like this at the end, hopefully. Notice how some of the OAN numbers have the same Risk Claim #, this is what is making it difficult.

[Jonmo1]

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

[sonic1019]

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

[Jonmo1]

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.

[Jonmo1]

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.