Formula to Lookup Partial text and result from same row different column

[Tianorth]

First of all can I apologise, I can read and understand formulas, but find it hard to write them.

I require a formula to return a value from a row (in a table) on another worksheet (Tab 1), where the table has partial text.
Tab 3 has full text (in cell) that I require the result to be copied.

The way I see the formula is :-
Tab3 cell U4 = Tab1 P:P (if Tab1 column C:C (partial text) is contained in Tab3 cell C4 (full text).

Hope this is clear.


[TABLE="width: 852"]
<colgroup><col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <col width="64" style="width: 48pt;" span="13"> <col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"> <col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 81, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Tab 3[/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"]Tab 1[/TD]
[TD="width: 95, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Full info[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, colspan: 2"]Partial info[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41101[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, colspan: 3"]This cell to return value of[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41101[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Printed[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41103[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, colspan: 4"]columnF in tab 1, if tab 1 column B:B[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41102[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Printed[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41315[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent, colspan: 4"]is contained within tab 3 column A[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41103[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Ready to print[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41102[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41104[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Held[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41416[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41105[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Started[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-61116[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41106[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Not allocated[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41614[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41107[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Complete[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41108[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41108[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Printed[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41190[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41109[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Not allocated[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41117[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41110[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Not allocated[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41150[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41111[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Not allocated[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41111[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41112[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Held[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41112[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41113[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Held[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41104[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41114[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Started[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41713[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41115[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Complete[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41174[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41116[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Complete[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-47115[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]41117[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Started[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]


I have been searching for a formula/help for the last 3 days, before finally registering on here for help.

Thank you in advance

Tia

 

[Fluff]

Hi & welcome to MrExcel.
How about
=INDEX(Sheet1!$F$2:$F$18,MATCH(--MID(A2,FIND("-",A2)+1,100),Sheet1!$B$2:$B$18,0))

 

[Tianorth]

I'm coming back with #VALUE ! when I put it in my spreadsheet and change the ranges.


If I understand the formula :-

This gives the results =INDEX(Sheet1!$F$2:$F$18) when the search text and partial text match

This is the search text (--MID(A2,FIND("-",A2)+1,100)

and this is where the partial text is ,Sheet1!$B$2:$B$18,0))


The area it is failing is the search text as my text includes spaces, dashes or no space.

Apologies for not having that in the example (now modified - see below).

[TABLE="width: 61"]
<colgroup><col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <tbody>[TR]
[TD="width: 81, bgcolor: transparent"]68974-41101[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974 41103[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41315[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6897441102[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41416[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-61116[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41614[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41108[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41190[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41117[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41150[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6897441111[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41112[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974 41104[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41713[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-41174
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]68974-47115[/TD]
[/TR]
</tbody>[/TABLE]

Tia

 

[Tianorth]

Apologies just seen this at bottom of thread.

Thank you for posting! Your post will not be visible until a moderator has approved it for posting.


Thought my reply had not sent!

Tia

 

[Fluff]

Will it always be the last five characters the need to be used for the search?

 

[Tianorth]

In this case yes but in other spreadsheets there could be another 3 or 5 numerics after (with or without spaces or dashes).

Thanks Tia

 

[Fluff]

In that case for this instance use
=INDEX(Sheet1!$F$2:$F$18,MATCH(--RIGHT(A2,5),Sheet1!$B$2:$B$18,0))

 

[Tianorth]

What do the 2 dashes do in the formula?

MATCH(--RIGHT

Thanks Tia

 

[Fluff]

The Right function returns a string, the -- coerces that string back into a number

 

[Tianorth]

Thank you that has worked.

Had to do a little editing of the partial text as first number had changed to text (I think) as it had a green triangle in the top corner of the cell.


Is there a way to get it to work with any length string, whether txt or numerics?

Thanks Tia