VLookup with Multiple Values

Manny74

Board Regular
Joined
May 6, 2016
Messages
103
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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....

Hygiene - Vlookup.JPG
 

Manny74

Board Regular
Joined
May 6, 2016
Messages
103
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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...


ABCDEF
1QuestionObservationNumDenComp %Department
2Is Dispenser filled?Hygiene By Dept
3​
3​
100​
8NT
3Is Dispenser filled?Hygiene By Dept
2​
3​
75​
10NT
4Is Paper Towel Dispenser filled?Hygiene By Dept
1​
1​
100​
8NT
5Is Paper Towel Dispenser filled?Hygiene By Dept
4​
4​
100​
10NT
6Did employee complete hand hygiene?Hygiene By Dept
3​
4​
75​
8NT
7Did employee complete hand hygiene?Hygiene By Dept
0​
3​
0​
10NT
8
98NTNumDenComp %
10Is Dispenser filled?
3​
3​
100​
11Is Paper Towel Dispenser filled?
1​
1​
100​
12Did employee complete hand hygiene?
3​
4​
75​
13
14
1510NT
16Is Dispenser filled?
2​
3​
75​
17Is Paper Towel Dispenser filled?
4​
4​
100​
18Did employee complete hand hygiene?
0​
3​
0​
 

Some videos you may like

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
Joined
Oct 24, 2015
Messages
7,499
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:

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499
I suggest to read
 

Manny74

Board Regular
Joined
May 6, 2016
Messages
103
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I suggest to read

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 LabelsSum of NumSum of DenCount 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
Joined
Oct 24, 2015
Messages
7,499
- using values field is not mandatory
- use PivotTable Tools - Design to set proper Layout
layouts.png
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,641
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

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