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

Tianorth

New Member
Joined
Apr 11, 2019
Messages
14
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.


Tab 3Tab 1
Full info Partial info
68974-41101 This cell to return value of 41101 Printed
68974-41103 columnF in tab 1, if tab 1 column B:B 41102 Printed
68974-41315 is contained within tab 3 column A 41103 Ready to print
68974-41102 41104 Held
68974-41416 41105 Started
68974-61116 41106 Not allocated
68974-41614 41107 Complete
68974-41108 41108 Printed
68974-41190 41109 Not allocated
68974-41117 41110 Not allocated
68974-41150 41111 Not allocated
68974-41111 41112 Held
68974-41112 41113 Held
68974-41104 41114 Started
68974-41713 41115 Complete
68974-41174 41116 Complete
68974-47115 41117 Started
<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> </tbody>


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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Apr 11, 2019
Messages
14
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).

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

Tia
 

Tianorth

New Member
Joined
Apr 11, 2019
Messages
14
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Tianorth

New Member
Joined
Apr 11, 2019
Messages
14
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
52,960
Office Version
  1. 365
Platform
  1. Windows
The Right function returns a string, the -- coerces that string back into a number
 

Tianorth

New Member
Joined
Apr 11, 2019
Messages
14
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,561
Messages
5,596,853
Members
414,107
Latest member
Tigretto

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top