IF / VLOOKUP

[Pippy79]

Hi All,

I have a question, I'm sure its a simple fix but I cant seem to work it out!

I have a look up table below which is my key for days and times.

	A	B	C	D
1		MP day - 2		Collection Day - 2
2	MP Day	Collection Day	Collection Time	Dispatch Notification
3	Mon	Sat	13:00	Thurs
4	Tue	Sun	13:00	Fri
5	Wed	Sun	13:00	Fri
6	Thu	Wed	11:00	Mon
7	Fri	Wed	11:00	Mon
8	Sat	Wed	11:00	Mon
9	Sun	Sat	13:00	Thurs

the start days are pulled from the below date.

MP Day

Wed, 02-Feb-22

Tue, 04-Jan-22

Fri, 07-Jan-22

Fri, 07-Jan-22

Tue, 11-Jan-22

Tue, 11-Jan-22

Using the table above "MP Day" I need to display if the using the key in the below table.

If MP day is Wed show dispatch notification as "Friday", Collection Day as "Sunday," Collection time as "13:00".

Dispatch Notification	Collection Day	Collection Time

Thanks
Phyl

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J
1		MP day - 2		Collection Day - 2			MP Day	Dispatch Notification	Collection Day	Collection Time
2	MP Day	Collection Day	Collection Time	Dispatch Notification			02/02/2022	Fri	Sun	13:00
3	Mon	Sat	13:00	Thurs			04/01/2022	Fri	Sun	13:00
4	Tue	Sun	13:00	Fri			07/01/2022	Mon	Wed	11:00
5	Wed	Sun	13:00	Fri			07/01/2022	Mon	Wed	11:00
6	Thu	Wed	11:00	Mon			11/01/2022	Fri	Sun	13:00
7	Fri	Wed	11:00	Mon
8	Sat	Wed	11:00	Mon
9	Sun	Sat	13:00	Thurs
10
Main

Cell Formulas
Range	Formula
H2:J6	H2	=INDEX(FILTER($B$3:$D$9,$A$3:$A$9=TEXT(G2,"ddd")),{3,1,2})
Dynamic array formulas.

 

[Pippy79]

Thanks Fluff,

what happens if the date is written as "Wed, 02-Feb-22" the calculation doesn't work. is it possible to work with this date format or only the format "02/02/2022"?

Thanks

 

[Fluff]

Are your MP dates text rather than proper dates?

 

[Pippy79]

yes i believe they are.

the cell is written as "Wed, 02-Feb-22", but I'm not sure if this is formatted from a date "02/02/22"

 

[Fluff]

If you select the cell & change the format to general, do you still see the text or a number?

 

[Pippy79]

it displays this "44594" when i change the format to general

 

[Fluff]

In that case it's a real date & the formula I suggested should work.

 

[Pippy79]

Apologies Fluff, my bad. this original formula works perfectly!!

Thanks

 

[Fluff]

You're welcome & thanks for the feedback.