Reverse Table Lookup using a partial string

[pat3white]

Okay what I am basically trying to do is to match a string of text with a full string of text and then take the law id to the right of it. I already have the formula to match, but i cannot find the right formula to find the position of that cell.
The formula I am using to match it is----

=if(sumproduct(--(isnumber(search(a1,D1:D3))),"match","false")

I just need to know how to find the position of the cell or how to obtain the law id to the right of it, if that is possible.
Thanks. Pat

[Richard Buttrey]

Hi,

Try

Please Login or Register  to view this content.

obviously change the ranges accordingly.

[pat3white]

Thanks Richard! I was very close with the index function, I was just using the sumproduct function instead of the match function.

[pat3white]

Quick question. What does the star asterisk signify?

[pat3white]

Okay, it hit a snag. The formula will work perfectly when the input is in the front, such as matching "william" to "William and Mary", but when I move "william" to the back of the string like "and mary law school william", it does not match. Any possible solutions?

Here is the updated worksheet.

[Richard Buttrey]

Hi,

Yes, just add an extra star!

Please Login or Register  to view this content.

The * is a wild card character which means in this case 'match any number of characters followed by the A1 value followed by the rest of the characters.

[pat3white]

Thanks a lot! I can't believe I have been using multiple and and or statements with the sumproduct(--(isnumber(search))) function and you just used the "*" and it worked the same. You have shorted my functions by a considerable amount. Have a great

[Richard Buttrey]

Glad to have helped and thanks for the rep.