# VLookup with Multiple Values

#### Manny74

##### Board Regular
Hello, this is a small sample of my data...
The large amount of data will vary from month to month with many more departments...
I like using VLOOKUP.....It's worked for individual departments....
I was able to use VLOOKUP for 8NT....For example, =VLOOKUP(A10,A2:C7,3,FALSE)

But I how would I use VLOOKUP to get, let's say the Numerator for question, Is Dispenser filled for 10NT?
I never used multiple values (Question & Department) in VLOOKUP....

#### Manny74

##### Board Regular
Hello All,

I'll try copying and pasting my excel report...

So like I saidm I was able to VLOOKUP for 8NT....For example, =VLOOKUP(A10,A2:C7,3,FALSE)

But how would I use VLOOKUP to get, let's say the Numerator, Denominator and Comp% for 10NT?

Under 10NT I entered the results I want to see, but I need to figure out how to create a VLOOKUP to get these results for 10NT?

I'm aslo toying with the idea of a pivot table, if I can make that work...

 A B C D E F 1 Question Observation Num Den Comp % Department 2 Is Dispenser filled? Hygiene By Dept 3​ 3​ 100​ 8NT 3 Is Dispenser filled? Hygiene By Dept 2​ 3​ 75​ 10NT 4 Is Paper Towel Dispenser filled? Hygiene By Dept 1​ 1​ 100​ 8NT 5 Is Paper Towel Dispenser filled? Hygiene By Dept 4​ 4​ 100​ 10NT 6 Did employee complete hand hygiene? Hygiene By Dept 3​ 4​ 75​ 8NT 7 Did employee complete hand hygiene? Hygiene By Dept 0​ 3​ 0​ 10NT 8 9 8NT Num Den Comp % 10 Is Dispenser filled? 3​ 3​ 100​ 11 Is Paper Towel Dispenser filled? 1​ 1​ 100​ 12 Did employee complete hand hygiene? 3​ 4​ 75​ 13 14 15 10NT 16 Is Dispenser filled? 2​ 3​ 75​ 17 Is Paper Towel Dispenser filled? 4​ 4​ 100​ 18 Did employee complete hand hygiene? 0​ 3​ 0​

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### sandy666

##### Banned - Rules violations
So I tried a pivot table....
Placed Department in Filters
Values in Column
Questions in Rows
Count of Den, Count of Num and Count of Overall Comp % in Values....I dont need the Count of Overall Comp % in Values, I need the %, the actual value.....
Right now I get this?
we will not get along if you do something completely different than what I show
have a nice day

Last edited:

#### Manny74

##### Board Regular

Alright, I reviewed this, and I did use Value Field Settings...But I can't figure out which calculation to choose....I've tried multiple calculations from the drop down and keep striking out....
For example, for question, Did DIETARY/TRAY PASSER complete hand hygiene?. 4NT had 2/3 which is 67%....But I get 1 as Count of Comp %, which is wrong...
What would be an appropriate calculation to select to get accurate percentages?

 Row Labels Sum of Num Sum of Den Count of Comp % Did ANESTHESIOLOGIST complete hand hygiene? 0​ 0​ 2​ 4NT 0​ 0​ 1​ 5NT 0​ 0​ 1​ Did CASE MANAGER/SOCIAL WORKER complete hand hygiene? 0​ 0​ 2​ 4NT 0​ 0​ 1​ 5NT 0​ 0​ 1​ Did DIETARY/TRAY PASSER complete hand hygiene? 2​ 3​ 2​ 4NT 2​ 3​ 1​ Should be 67% 5NT 0​ 0​ 1​ Did EVS complete hand hygiene? 0​ 0​ 2​ 4NT 0​ 0​ 1​ 5NT 0​ 0​ 1​ Did MEDICAL STUDENT complete hand hygiene? 0​ 0​ 2​ 4NT 0​ 0​ 1​ 5NT 0​ 0​ 1​ Did NURSE complete hand hygiene? 3​ 4​ 2​ 4NT 0​ 0​ 1​ 5NT 3​ 4​ 1​ Should be 75% Did NURSING SUPPORT/PCA complete hand hygiene? 3​ 4​ 2​ 4NT 3​ 4​ 1​ Should be 75% 5NT 0​ 0​ 1​ Did OTHER staff complete hand hygiene? 0​ 0​ 2​ 4NT 0​ 0​ 1​ 5NT 0​ 0​ 1​ Did PA/ARNP complete hand hygiene? 0​ 0​ 2​ 4NT 0​ 0​ 1​ 5NT 0​ 0​ 1​ Did PHYSICIAN complete hand hygiene? 1​ 1​ 2​ 4NT 0​ 0​ 1​ 5NT 1​ 1​ 1​ Did PT/OT complete hand hygiene? 0​ 0​ 2​ 4NT 0​ 0​ 1​ 5NT 0​ 0​ 1​ Did RESIDENT complete hand hygiene? 7​ 8​ 2​ 4NT 5​ 5​ 1​ Should be 100% 5NT 2​ 3​ 1​ Should be 67% Did RESPIRATORY THERAPIST complete hand hygiene? 0​ 0​ 2​ 4NT 0​ 0​ 1​ 5NT 0​ 0​ 1​ Did SURGEON complete hand hygiene? 0​ 0​ 2​ 4NT 0​ 0​ 1​ 5NT 0​ 0​ 1​ Did TRANSPORTATION complete hand hygiene? 0​ 0​ 2​ 4NT 0​ 0​ 1​ 5NT 0​ 0​ 1​ Is Alcohol/Soap Dispenser filled? 3​ 3​ 2​ 4NT 0​ 0​ 1​ 5NT 3​ 3​ 1​ Is Paper Towel Dispenser filled? 1​ 1​ 2​ 4NT 0​ 0​ 1​ 5NT 1​ 1​ 1​ Grand Total 20​ 24​ 34​

#### sandy666

##### Banned - Rules violations
- using values field is not mandatory
- use PivotTable Tools - Design to set proper Layout

##### Well-known Member
For First Post without Pivot tables, you can use this formula:
Also Remove Soap from A16 to find your criteria at range.
At B16:
Excel Formula:
``=SUMIFS(C\$2:C\$7,\$A\$2:\$A\$7,\$A16,\$F\$2:\$F\$7,\$A\$15)``
Then Drag it right & down.

Replies
7
Views
74
Replies
1
Views
115
Replies
2
Views
115
Replies
4
Views
174
Replies
5
Views
184

1,127,668
Messages
5,626,180
Members
416,166
Latest member
Archimed

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

### Which adblocker are you using?

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

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