VLookup with Multiple Values

[Manny74]

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]

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​

 

[sandy666]

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

 

[sandy666]

I suggest to read

Pivot Tables in Excel

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.

www.excel-easy.com

 

[Manny74]

I suggest to read

Pivot Tables in Excel

Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.

www.excel-easy.com

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]

- using values field is not mandatory
- use PivotTable Tools - Design to set proper Layout

 

[maabadi]

For First Post without Pivot tables, you can use this formula:
Also Remove Soap from A16 to find your criteria at range.
At B16:

=SUMIFS(C$2:C$7,$A$2:$A$7,$A16,$F$2:$F$7,$A$15)

Then Drag it right & down.