vlookup help

znaya

Board Regular
Joined
Apr 22, 2006
Messages
55
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.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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

Board Regular
Joined
Apr 22, 2006
Messages
55
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
Here's one way:

Code:
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","")))

Code:
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
ABCDEFGHI
1ListingSum of Group ASum of Group BGroup AValueGroup BValue
2M10M1Me4
3Ne05N2Ne5
4MN30T3Te6
5MeNe09
6MNeT45
Sheet1
 

znaya

Board Regular
Joined
Apr 22, 2006
Messages
55
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,010
Office Version
  1. 365
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.
 

Forum statistics

Threads
1,137,294
Messages
5,680,660
Members
419,923
Latest member
Kalthus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top