vlookup help

[znaya]

hello!

i have a problem and i hope you can help me...

i'm building a spreadsheet where there is a list where users will input work shifts (those shift names can be, for example, "M", "N", "T", "Me", "Te", "Ne"...

i have two different tables, one for getting values for the shifts "M", "N" and "T", the other for getting the values for the shifts "Me", "Ne" and "Te".
the values are count in separate columns through vlookup functions.

is there a simple way to get the values if, for example, a user needs to input two shifts in the same day?
theorically the user can input any two shifts to a maximum of 3 shifts in a day

if "MT", then i want to sum the value of vlookup "M" + value of vlookup "T"
if "MeT", then i want to get the value of vlookp "Me" in a column and the value of vlookup "T" in another column. the values of "Me" and "T" cannot be summed.

 

[Oaktree]

So... what are all of the rules for what can be summed? Is it that a ?e shift can't be summed with a non ?e shift? You said "the user can input any two shifts to a maximum of 3 shifts"... does that mean you could have a MNT or similar combination as well?

Just list our the rules for when things should get summed and when you want them in two (or three) columns (what happens if there's an MeNT?) and I'm sure someone will be able to come up with a solution for you...

 

[znaya]

MNT can be summed.
MeNeTe can be summed.

so far, what i've done to get the values are two columns (B and C) with vlookup functions. one column (B) vlooksup MNT, the other (C) vlooksup MeNeTe.

this seems to work fine if a user has only one shift per day.

MNT can be summed together.
MeNeTe ca be summed together.
these two groups cannot be summed together (that's why i have two different columns to vlookup its values).

imagine a user inputs "MNeT" i want to:
in column B, sum the values of vlookup M and T
in column C vlookup the value of Ne.

imagine a user inputs "MeNeT" i want to:
in column B vlookup the value of T.
in column C, sum the values of vlookup Me and Ne

thanks in advance.

 

[Oaktree]

Here's one way:

B2=$F$2*ISNUMBER(SEARCH("M",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Me",""),"Ne",""),"Te","")))+$F$3*ISNUMBER(SEARCH("N",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Me",""),"Ne",""),"Te","")))+$F$4*ISNUMBER(SEARCH("T",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,"Me",""),"Ne",""),"Te","")))

C2 =$I$2*ISNUMBER(SEARCH("Me",A2))+$I$3*ISNUMBER(SEARCH("Ne",A2))+$I$4*ISNUMBER(SEARCH("Te",A2))

Copy B2 and C2 down through the end of your range.

Book3
	A	B	C	D	E	F	G	H	I
1	Listing	Sum of Group A	Sum of Group B		Group A	Value		Group B	Value
2	M	1	0		M	1		Me	4
3	Ne	0	5		N	2		Ne	5
4	MN	3	0		T	3		Te	6
5	MeNe	0	9
6	MNeT	4	5
Sheet1

 

[znaya]

hmmm...

it's not that what i want... i'm sorry. my english is not perfect and it surely does not help explaining.

what i want to sum is the values of the vlookup function, not the number of shifts. the values of the shift depend on weekdays, that's why i cannot sum the number of shifts.

for the MNT shifts column i have:
VLOOKUP(A1;$values.$A$1:$D$3;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2)))

for the MeNeTe shifts column i have:
IF(ISNA(VLOOKUP(A1;$values.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0));0;VLOOKUP(A1;$values.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0))

but this works only for one shift per day.

for more than one shift i wanted to vlookup[...]+vlookup[...]...
one solution would be to write an extremely long funciton in which i would preview every scenario....

i hope i explained better my idea...

 

[Oaktree]

I think the same idea applies though...

Instead of =$F$2*..., you'd want to change $F$2 to IF(WEEKDAY(G3)=1,D67,IF(WEEKDAY(G3)=7,C67,B67)) etc.

Basically, you'll have a two monster formulas (one in column B and one in column C) which will each sum three parts of:
1/0 depending on whether each M/N/T/Me/Ne/Te value is found by the corresponding value for each weekday and M/.../Te combination.