# vlookup help

#### znaya

##### Board Regular
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### Oaktree

##### MrExcel MVP
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
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

#### Oaktree

##### MrExcel MVP
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
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
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.

Replies
6
Views
166
Replies
6
Views
373
Replies
1
Views
233
Replies
4
Views
376
Replies
3
Views
149

### Forum statistics

1,175,510
Messages
5,897,858
Members
434,682
Latest member
p1nkman ### 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?    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