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:-
[TABLE="width: 392"]
<tbody>[TR]
[TD]Inward Date[/TD]
[TD]Inward Day[/TD]
[TD]Schedule Type[/TD]
[TD]Inward Day-Desired(With Formula)[/TD]
[/TR]
[TR]
[TD]01-Aug[/TD]
[TD]Tuesday[/TD]
[TD]A[/TD]
[TD]Tuesday[/TD]
[/TR]
[TR]
[TD]02-Aug[/TD]
[TD]Wednesday[/TD]
[TD]B[/TD]
[TD]Wednesday [/TD]
[/TR]
[TR]
[TD]04-Aug[/TD]
[TD]Friday[/TD]
[TD]C[/TD]
[TD]Friday[/TD]
[/TR]
</tbody>[/TABLE]

There is another table with the Schedule Type across F1:G4 as follows:-
[TABLE="width: 366"]
<tbody>[TR]
[TD]Schedule Type[/TD]
[TD]Description[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]Tuesday, Thursday[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]Monday, Saturday, Wednesday[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Friday[/TD]
[/TR]
</tbody>[/TABLE]

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.