Multiple Drop Down Lookup

[Adam Schaefer]

I am looking to accomplish 2 items in this tip sheet for the bartender group outlined with a thick grid line. The bar section is seperated into am BAR (green) and pm BAR (pink).

My goal is this: If a bartender works a double I need to be able to add the AM and PM tips together. So, I have created a drop down list in N21 (highlighted yellow). If I select the bartender's name who has worked a double, row 21 (Columns j, k, l, and m,) are populated with the addition of the AM and PM tips for that particular bartender.

For example: If Steve has worked in both the AM and PM (shown on sheet), row 21 would read 175, 84, 259, and 150 for columns j, k, l, and m.

The second item seems simple. I would like to add to a formula. If any time I select a PM bartender (pink), it automatically populates the PM SA column (Column P) with $10.00.

Thanks for any help, if you have any ideas, I am unable to download excel files due to my works firewall.Tips.xlsx

[FDibbins]

The workbook you uploaded contans no data, only the table?

[Adam Schaefer]

Maybe this will work better. Still the same question.Tips.xlsx

[FDibbins]

For the 1st part, add this onto the end...
+IF($N$21=$H22,VLOOKUP($N$21,$H$26:$M$29,3),0)

[FDibbins]

For the 2nd part, try this in P6, copied down...
=IF(COUNTA($H$26:$I$29)>0,10,0)
(although you have $50 in that column now?)

[FDibbins]

Thanks, that gives me something to work with And I can now also relate to the values you mentioned

[Adam Schaefer]

Tips.xlsmI'm not quite sure if I need to append the first formula to each cell in row 21 (j,k,l,m)?

The 2nd formula is producing the $10 in column P for all 4 bartenders even though I only have 2 selected.

[FDibbins]

The reason I suggested that you copy it down for all "green" rows was to cater for more than 1 EE working OT, but I Guess that wont happen, so just use it for the 1st row only.

Neither of the names in the pink area actually match the names in B or I (they need to match exactly - Steve wont match Steve G), but I tested with "Ricky" and this worked...
=IF(ISERROR(VLOOKUP($B4,$H$26:$I$29,1,0)),0,10)

[Adam Schaefer]

Awesome. Columns B and I are both servers, column H is just bartenders. The thought process is that if a bartender works at night, the busser (SA) does a few things for them at the end of the evening, and the bartender tips them out $10. So if I select a bartender, I just want it to populate $10 right off the bat.

[Adam Schaefer]

What if I word it this way? If I select a name in N21, where ever that name appears in H22:29, cells j,k,l,m, in that speecific name's row are added to row 21.

-or- if a name appears twice in H22:H29 they are added together in row 21?