Extracting data from text

[North for Short]

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?

[TABLE="width: 793"]
<colgroup><col><col><col><col span="6"></colgroup><tbody>[TR]
[TD]duty[/TD]
[TD]Duty Holder[/TD]
[TD]Payroll[/TD]
[TD]MONDAY[/TD]
[TD]TUESDAY[/TD]
[TD]WEDNESDAY[/TD]
[TD]THURSDAY[/TD]
[TD]FRIDAY[/TD]
[TD]SATURDAY[/TD]
[/TR]
[TR]
[TD]route 1[/TD]
[TD]Casey Jones[/TD]
[TD]1001[/TD]
[TD]Jack Sparrow[/TD]
[TD]Casey Jones[/TD]
[TD]Casey Jones[/TD]
[TD]Casey Jones[/TD]
[TD]Casey Jones[/TD]
[TD]Casey Jones[/TD]
[/TR]
[TR]
[TD]route 2[/TD]
[TD]Robin Hood[/TD]
[TD]1002[/TD]
[TD]Robin Hood[/TD]
[TD]Jack Sparrow[/TD]
[TD]Robin Hood[/TD]
[TD]Robin Hood[/TD]
[TD]Robin Hood[/TD]
[TD]Robin Hood[/TD]
[/TR]
[TR]
[TD]route 3[/TD]
[TD]Benny Hill[/TD]
[TD]1003[/TD]
[TD]Benny Hill[/TD]
[TD]Benny Hill[/TD]
[TD]Jack Sparrow[/TD]
[TD]Benny Hill[/TD]
[TD]Benny Hill[/TD]
[TD]Benny Hill[/TD]
[/TR]
[TR]
[TD]route 4[/TD]
[TD]Mary Shelley[/TD]
[TD]1004[/TD]
[TD]Mary Shelley[/TD]
[TD]Mary Shelley[/TD]
[TD]Mary Shelley[/TD]
[TD]Mary Shelley[/TD]
[TD]Jack Sparrow[/TD]
[TD]Mary Shelley[/TD]
[/TR]
[TR]
[TD]route 5[/TD]
[TD]Henry Morgan[/TD]
[TD]1005[/TD]
[TD]Henry Morgan[/TD]
[TD]Henry Morgan[/TD]
[TD]Henry Morgan[/TD]
[TD]Henry Morgan[/TD]
[TD]Henry Morgan[/TD]
[TD]Jack Sparrow[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Reserve[/TD]
[TD]Payroll[/TD]
[TD]MONDAY[/TD]
[TD]TUESDAY[/TD]
[TD]WEDNESDAY[/TD]
[TD]THURSDAY[/TD]
[TD]FRIDAY[/TD]
[TD]SATURDAY[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]Jack Sparrow[/TD]
[TD]1006[/TD]
[TD]route 1[/TD]
[TD]?[/TD]
[TD]?[/TD]
[TD]day off[/TD]
[TD]?[/TD]
[TD]?
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Is this, copied across, what you want. If not, some more explanation and expected results please.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	duty	Duty Holder	Payroll	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
2	route 1	Casey Jones	1001	Jack Sparrow	Casey Jones	Casey Jones	Casey Jones	Casey Jones	Casey Jones
3	route 2	Robin Hood	1002	Robin Hood	Jack Sparrow	Robin Hood	Robin Hood	Robin Hood	Robin Hood
4	route 3	Benny Hill	1003	Benny Hill	Benny Hill	Jack Sparrow	Benny Hill	Benny Hill	Benny Hill
5	route 4	Mary Shelley	1004	Mary Shelley	Mary Shelley	Mary Shelley	Mary Shelley	Jack Sparrow	Mary Shelley
6	route 5	Henry Morgan	1005	Henry Morgan	Henry Morgan	Henry Morgan	Henry Morgan	Henry Morgan	Jack Sparrow
7
8
9		Reserve	Payroll	MONDAY	TUESDAY	WEDNESDAY	THURSDAY	FRIDAY	SATURDAY
10		Jack Sparrow	1006	route 1	route 2	route 3	day off	route 4	route 5
Which Route

 

[North for Short]

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]

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]

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

 

[Peter_SSs]

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

Cheers.

 

[North for Short]

No Cheers to you. Have a long cool one.