Formula

[xxsalahxx]

Hello I have a spreadsheet which has quite a lot of data in it .

I would like it to be able when adding WD 1, WD 2, WD 3, WD 4 into column A that Column B auto populates to say Medical and for Column C to say Medicine is this possible?
and WD 5 WD 6 WD 7 to say Surgical in column B and Surgery in column C

Is this possible?
Thanking you in advance for any help is much appreciated.

Input	OutPut	OutPut1
Ward 1	Medical	Medicine
Ward 2	Medical	Medicine
Ward 2	Medical	Medicine
Ward 3	Medical	Medicine
Ward 4	Medical	Medicine
Ward 4	Medical	Medicine
Ward 5	Surgical	Surgery
Ward 6	Surgical	Surgery
Ward 7	Surgical	Surgery

 

[xxsalahxx]

Hello
Sorry for asking again, im just wondering if i can expand more as I have a ward that is named Ward15a and ward 15b and also one that is names Acute Medical, the formula is not picking up if i have a letter after the ward number and also if its text only.
Is there a way around this?
working with excel 2007

Thanking you in advance

 

[Sufiyan97]

Try with helper table

Book2
	A	B	C	D	E	F	G	H	I	J
1	Input	OutPut	OutPut1				Input	OutPut	OutPut1
2	Ward 1	Medical	Medicine				Ward 1	Medical	Medicine
3	Ward 2	Medical	Medicine				Ward 2	Medical	Medicine
4	Ward 2	Medical	Medicine				Ward 3	Medical	Medicine
5	Ward 3	Medical	Medicine				Ward 4	Medical	Medicine
6	Ward 4	Medical	Medicine				Ward 5	Surgical	Surgery
7	Ward 4	Medical	Medicine				Ward 6	Surgical	Surgery
8	Ward 5	Surgical	Surgery				Ward 7	Surgical	Surgery
9	Ward 6	Surgical	Surgery				Ward15a	Medical	Surgery
10	Ward 7	Surgical	Surgery
11	Ward15a	Medical	Surgery
12
13
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=VLOOKUP($A2,$G$1:$I$9,2,0)
C2:C11	C2	=VLOOKUP($A2,$G$1:$I$9,3,0)

 

[Fluff]

Please post some accurate data of what you have & what you want.

 

[xxsalahxx]

Please see below of what I have but i have thousands of rows of data.
The formula I have is working fine until I reach Ward 15a, Ward 15b and anything that only has text such as acute medical. What im looking for is when someone enters Ward 1 the next column updates to Medical and the columns after that updates to Medicine. The same for ward 15a and 15b and acute medical. any help is appreciated.

Input	OutPut	OutPut1
Ward 1	Medical	Medicine
Ward 2	Medical	Medicine
Ward 2	Medical	Medicine
Ward 3	Medical	Medicine
Ward 4	Medical	Medicine
Ward 4	Medical	Medicine
Ward 5	Surgical	Surgery
Ward 6	Surgical	Surgery
Ward 15a	Surgical	Surgery
Ward 15b	Surgical	Surgery
Acute Medical	Medical	Medicine

 

[Fluff]

Why not use use vlookup to lookup the input & compare to the data you just posted.

 

[xxsalahxx]

I will give this a try never used vlookup before so quite a novice here, I will let you know how i get on with that. thank you

 

[Fluff]

Ok

 

[xxsalahxx]

It works!! thank you so much WOW!!

 

[Fluff]

Glad we could help & thanks for the feedback.