Index/Match with multiple criteria and IF function

[SteveNL86]

Dear reader,

I'm trying to match 2 tables to quickly put the right amount of hours behind a certain shift.

So I got this table with the hours:

Shift	Ma	Di	Wo	Do	Vr	Za	Zo	Fd/fst
AW	5,5	5,5	5,5	5,5	5,5	24	24	24
NAC	5,5	5,5	5,5	5,5	5,5	6	7	7
NAV	6	6	6	6	6	7	7	7
NDC1	0	0	0	0	0	8	8	8

And I got an employee shift page like this:

Date	Day	Code	Shift	Hours
09-01-2018	di	AW	AW wk	5,5
21-01-2018	zo	NAC	NAC fd	7
02-02-2018	vr	NAC	NAC wk	5,5
08-03-2018	do	NAV	NAV wk	6
17-03-2018	za	NDC1	NDC1 wknd	8
04-04-2018	za	NAC	NAC wknd	6

So if the shift column contains "fst" or "fd" it should grab the hours from the fd/fst column corresponding to the right shift code. If the shift description doesn't contain "fd" or "fst" it should grab the hours from the corresponding day and shiftcode.

I'm having trouble getting this to work in hours column.
Any help is much appreciated!

 

[Fluff]

I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Is you table of shift hours a structured table or just a normal range?

 

[SteveNL86]

Thanks Fluff, I've done so immediatly.

The table is just a normal range.

 

[Fluff]

Ok, thanks for that, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I
1	Shift	Ma	Di	Wo	Do	Vr	Za	Zo	Fd/fst
2	AW	5.5	5.5	5.5	5.5	5.5	24	24	24
3	NAC	5.5	5.5	5.5	5.5	5.5	6	7	7
4	NAV	6	6	6	6	6	7	7	7
5	NDC1	0	0	0	0	0	8	8	8
Main

+Fluff v2.xlsm
	M	N	O	P	Q
1	Date	Day	Code	Shift	Hours
2	09/01/2018	di	AW	AW wk	5.5
3	21/01/2018	zo	NAC	NAC fd	7
4	02/02/2018	vr	NAC	NAC wk	5.5
5	08/03/2018	do	NAV	NAV wk	6
6	17/03/2018	za	NDC1	NDC1 wknd	8
7	04/04/2018	za	NAC	NAC wknd	6
Main

Cell Formulas
Range	Formula
Q2:Q7	Q2	=INDEX($B$2:$I$5,MATCH(O2,$A$2:$A$5,0),IF(MAX(--ISNUMBER(SEARCH({"fd","fst"},P2))),8,MATCH(N2,$B$1:$I$1,0)))

 

[SteveNL86]

Thanks Fluff!

I'm struggling a little bit getting it to work in my worksheet. After adapting it to my sheets it does grab the right hours on the sundays (zo) but I get a #N/a in every other row.

 

[SteveNL86]

I figured it out. Amazing! Very much appreciated Fluff, your awesome!

 

[Fluff]

Glad you sorted it & thanks for the feedback.