Thread: need help vlookingup

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 can be, for example, "M", "N", "T", "Me", "Ne", "Te")...

i use two different columns to retrieve (using vlookup) values for M, N, T and Me, Ne, Te shifts. those values are in two different tables (one table for M, N, T shifts and another one for Me, Ne, Te)

for the M, N, T shifts column i use:
VLOOKUP(A1;$values.$A$1:$D$3;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2)))

for the Me, Ne, Te 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;$v alues.$A$67:$D$69;IF(WEEKDAY(G3)=1;4;IF(WEEKDAY(G3)=7;3;2));0))

this seems to works fine, but only for one shift per day.
the problem is that workers may work more than one shift per day...

is there a simple way of, in one column, vlooking up the values for every M, N, T shift, every day ignoring any Me, Ne, Te and the opposite in the other column?

my idea is:

if a user inputs "MNeT" i want to:
in one column, sum the values of vlookup M and T
in the other column vlookup the value of Ne.

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

thanks in advance

I have this exact quandry. I have employees who are working on two "shifts" and I need to somehow vlookup but separate by "shift".