How To Match & Pick-Up Partial Text From A Cell Conditionally?

[xlmaniac]

Dear All,
I do have a sample data set across A1:D4 as follows:-

Inward Date	Inward Day	Schedule Type	Inward Day-Desired(With Formula)
01-Aug	Tuesday	A	Tuesday
02-Aug	Wednesday	B	Wednesday
04-Aug	Friday	C	Friday

<tbody>
</tbody>

There is another table with the Schedule Type across F1:G4 as follows:-

Schedule Type	Description
A	Tuesday, Thursday
B	Monday, Saturday, Wednesday
C	Friday

<tbody>
</tbody>

I am looking at formulae across column D2:D4, which will look at the Inward day(Column B) and then match the schedule type(across column C) with that of the schedule type maintained across F1:G4 and then will populate the day from that.
If there are multiple days mapped under a Schedule Type(Like A &B), the formulae should pick-up the day corresponding day against the inward day.
In case of no matching, it should keep the cell blank under column D.
Pls help.
Thanks

 

[Aladin Akyurek]

In D2 enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B2,INDEX($G$2:$G$4,MATCH($C2,$F$2:$F$4,0))))),$B2,"")

 

[SyedUsman]

In D2 enter and copy down:

=IF(ISNUMBER(SEARCH(B2,LOOKUP(C2,$F$2:$F$4,$G$2:$G$4))),B2,"")

 

[xlmaniac]

In D2 enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,SEARCH($B2,INDEX($G$2:$G$4,MATCH($C2,$F$2:$F$4,0))))),$B2,"")

Dear Mr Aladin & Usman,
Thanks a lot for the amazing solution.
It really will save lots of unwanted manual intervetion.
My heartfelt gratitude to both of you.
Proud to be here in this forum.
Regards

 

[Aladin Akyurek]

@xlmaniac

If F:G is not sorted in ascending order on column F, the second proposal won't work.

 

[xlmaniac]

@xlmaniac

If F:G is not sorted in ascending order on column F, the second proposal won't work.

Thanks a lot Sir for the input.
I will surely do the needful accordingly.