Pre-populate cell based on text in another cell! VLOOKUP not working :(

Hello!!

I have column B that has data validation in very cell. Users can choose an item from that list. When they choose a text item, I would like a specific number to display in Column D. All text items will have a number assigned to them and I'd like to automatically display in the column (same row). I have set up a table on the same sheet as follows:

SW Mid 12
SW Night 10
RN Night 10
RN Early 7.5
RN Mid 7.5
RN Late 7.5
SW Early 6
SW Late 6



When I use function =VLOOKUP(B2,Y243:Z250,2) on row 2 (or any of them) - it's not working. Some cells have worked but most of them say N/A or it's not displaying the correct number.
Also - is there a way of writing the function so that if the item on Column B ever gets changed the number in Column D will automatically update?


Heeeellllppppp

Thank you in advance.

Hi both

So I've attached the spreadsheet (its a copy - unfortunately I couldn't remove the sensitive data from my actual sheet as it's my working version.)

What I would like it to do in an ideal world....

1. When I select the 'Shift' - I would like the hours of that shift to appear in the 'Hours' Column

2. When I select 'Y' in 'Picked up Internally' - I'd like the whole row to turn green and the text to turn black.





Many thanks in advance

D2=IFERROR(VLOOKUP($B2,$Q$51:$R$58,2,0),"")

copy down


For conditional formatting

=$G2="Y" applies to =$A$2:$N$53

Fill green

Font style bold

Color black

YES - thanks everyone!!!!!!


THIS IS THE BEST FORUM EVER!!!!!

Worked a treat!!!!

Thank you so much :-D




Next question:

I really need to be able to record somewhere on the sheet how many hours were picked up internally. So: If I select 'Y' in column G - I need to do a count of the hours and add them up as a total for the whole month.
But for every 'blank' in column G - I also need to record the hours from column D

Does that make sense?

e.g.

Hours covered internally: 22.5
Hours covered externally: 37.5

Thank you so much.... again! Much appreciated

YES!!!! Legend! Thank you

My final question then.... for now..

Is as follows..

I am recording shifts that need covering in a care home. Each shift needs to be covered for a specific reason which is from a drop down list I have set up on each row.

Is there a way of working out how many hours Column D were because of each reason across the month.

For example

October 2019

500 Sickness
200 Annual
100 Maternity


etc etc.... does that make sense?

Thank you again.

Looks like this:

10/10/2019 Early 6 Sickness
10/10/2019 Late 4.5 Sickness
11/10/2019 Late 6 Annual Leave
12/10/2019 Early 5 Maternity

Need it to display like this:

Total Hours for October

Annual Leave 6
Sickness 10.5
Maternity 5

Attach the file and indicate the expected result

	Q	R	S	T	U	V
1		Annual Leave	Sickness	Maternity	EO121	Vacancy
2	Early	12	6	0	6	12
3	Mid	31.5	0	0	48	0
4	Late	25.5	18	0	0	13.5
5	Night	80	0	10	10	30

R2=SUMIFS($D$2:$D$53,$B$2:$B$53,"*"&$Q2,$N$2:$N$53,R$1)

Copy across and down

Post # 5 file

YES YES YES!!!! Wahooo…. thank you