Extracting data from text

North for Short

New Member
Joined
Nov 17, 2010
Messages
46
I have a Spreadsheet which I have linked to other sheets effectively, (thanks to this board). I now want to go one step further and be able to write in a name in a column and within the same column, return a number (in this case a duty no.) I have tried Vlookup, if and match and so far, I have drawn a blank on obtaining the correct information. Is there anyway that this can be done? I have pasted a small extract with ficticious names. What I want is to know what route Jack Sparrow is doing on tuesday. I will leave it with you guys. If this has been done before, can someone point me in the right direction?

dutyDuty HolderPayrollMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
route 1Casey Jones1001Jack SparrowCasey JonesCasey JonesCasey JonesCasey JonesCasey Jones
route 2Robin Hood1002Robin HoodJack SparrowRobin HoodRobin HoodRobin HoodRobin Hood
route 3Benny Hill1003Benny HillBenny HillJack SparrowBenny HillBenny HillBenny Hill
route 4Mary Shelley1004Mary ShelleyMary ShelleyMary ShelleyMary ShelleyJack SparrowMary Shelley
route 5Henry Morgan1005Henry MorganHenry MorganHenry MorganHenry MorganHenry MorganJack Sparrow
ReservePayrollMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
Jack Sparrow1006route 1??day off??

<colgroup><col><col><col><col span="6"></colgroup><tbody>
</tbody>
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,858
Office Version
  1. 365
Platform
  1. Windows
Is this, copied across, what you want. If not, some more explanation and expected results please.

Excel Workbook
ABCDEFGHI
1dutyDuty HolderPayrollMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
2route 1Casey Jones1001Jack SparrowCasey JonesCasey JonesCasey JonesCasey JonesCasey Jones
3route 2Robin Hood1002Robin HoodJack SparrowRobin HoodRobin HoodRobin HoodRobin Hood
4route 3Benny Hill1003Benny HillBenny HillJack SparrowBenny HillBenny HillBenny Hill
5route 4Mary Shelley1004Mary ShelleyMary ShelleyMary ShelleyMary ShelleyJack SparrowMary Shelley
6route 5Henry Morgan1005Henry MorganHenry MorganHenry MorganHenry MorganHenry MorganJack Sparrow
7
8
9ReservePayrollMONDAYTUESDAYWEDNESDAYTHURSDAYFRIDAYSATURDAY
10Jack Sparrow1006route 1route 2route 3day offroute 4route 5
Which Route
 
Solution

North for Short

New Member
Joined
Nov 17, 2010
Messages
46
Jack Sparrow is a reserve person and I want to know what duty (route) he will be performing. So the top table contains the duty holders and where Jack sparrow is, the reserve pool. (There are 10 names). So, if I write in against Casey Jones in the top table, I want expecting a numerical value in the bottom table. So where I put a question Mark in on the tuesday, I know it is going to be route 2 but instead of writing it in, I want the formula to do it for me. Does this help? (the last formula only returned data that stated day off).
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,858
Office Version
  1. 365
Platform
  1. Windows
So where I put a question Mark in on the tuesday, I know it is going to be route 2 but instead of writing it in, I want the formula to do it for me. Does this help?
I'm not sure I am understanding what you have said. For me the formula is returning "route 2" for Tuesday. I entered the formula shown in D10 of my layout and copied it across to I10. The values shown in my screen shot for D10:I10 were all produced by that formula.
 

North for Short

New Member
Joined
Nov 17, 2010
Messages
46

ADVERTISEMENT

That worked. It would help if I tried it on the spreadsheet I sent you and not the real Mccoy. Many thanks
 

Forum statistics

Threads
1,148,294
Messages
5,745,942
Members
423,985
Latest member
sayed manzar

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