Excel VLOOKUP: Handling Multiple Lookup Values and Building Relationships in Data Model, Exercises of MS Microsoft Excel skills

Download Excel VLOOKUP: Handling Multiple Lookup Values and Building Relationships in Data Model and more Exercises MS Microsoft Excel skills in PDF only on Docsity!

Highline Excel 2016 Class 12-13: Topics: 12 13 14

Highline Excel 2016 Class 12-13: Two Lookup Values in VLOOKUP? Helper Column or Array Formula. One Lookup Value to Return Multiple Items: Array Formula Think of VLOOKUP as Relationship in Data Model and Vice Versa

Array Formula NO CSE Status Status Complete Review Complete Not Yet Reviewed Not Yet Reviewed

Highline Excel 2016 Class 12: Two Lookup Values in VLOOKUP? Helper Column or Array Formula. Unique Identifier Server Name Event Status ZZR45678AEVENT-1 ZZR45678A EVENT-1 Complete ZZR45678AEVENT-2 ZZR45678A EVENT-2 Review BBT5643BGEVENT-1 BBT5643BG EVENT-1 Complete BBT5643BGEVENT-2 BBT5643BG EVENT-2 Not Yet Reviewed BBT5643BGEVENT-3 BBT5643BG EVENT-3 Not Yet Reviewed N-987-HQ5SSDEVENT-1 N-987-HQ5SSD EVENT-1 Incomplete N-987-HQ5SSDEVENT-2 N-987-HQ5SSD EVENT-2 1/2 Stage Complete Helper Column Array Formula w CSE Server Name Event Status Status ZZR45678A EVENT-1 Complete Complete ZZR45678A EVENT-2 Review Review BBT5643BG EVENT-1 Complete Complete BBT5643BG EVENT-2 Not Yet Reviewed Not Yet Reviewed BBT5643BG EVENT-3 Not Yet Reviewed Not Yet Reviewed

Highline Excel 2016 Class 13: One Lookup Value to Return Multiple Items: Array Formula Server Name Event Status Server Name ZZR45678A EVENT-1 Complete N-987-HQ5SSD ZZR45678A EVENT-2 Review BBT5643BG EVENT-1 Complete Count BBT5643BG EVENT-2 Not Yet Reviewed 2 BBT5643BG EVENT-3 Not Yet Reviewed N-987-HQ5SSD EVENT-1 Incomplete Events AGGREGATE(15,6,(ROW($A$5:$A N-987-HQ5SSD EVENT-2 1/2 Stage Complete EVENT- EVENT-

Server Name ZZR45678A BBT5643BG N-987-HQ5SSD AGGREGATE(15,6,(ROW($A$5:$A$11)-ROW($A$5)+1)/($A$5:$A$11=$E$5),ROWS(E$11:E11))

Server Name ZZR45678A BBT5643BG N-987-HQ5SSD

Highline Excel 2016 Class 14: Think of VLOOKUP as Relationship in Data Model and Vice Versa Date SalesRep Sales Column1 SalesRep 5/28/2016 Sioux $1,537 Canada Sioux 5/16/2016 Sioux $5,959 Canada Han 5/25/2016 Kip $1,380 South America Kip 5/21/2016 Sioux $6,652 Canada 5/23/2016 Han $10,690 United States 5/31/2016 Han $1,513 United States VLOOKUP: 5/13/2016 Sioux $8,767 Canada 2) Your data sets are already in t 5/25/2016 Kip $7,921 South America 5/25/2016 Sioux $8,419 Canada 5/16/2016 Sioux $2,769 Canada Relationship in Data Model: 5/30/2016 Sioux $3,439 Canada 5/26/2016 Sioux $1,036 Canada 5/27/2016 Han $7,578 United States 3) DAX Formulas can be built on

1. When data set is not big
2. Quick and easy solution for 1
3. You have big data
4. You need to build a complex

Create a solution so that the user can select a room and bldg to lookup whether or not the room has a web cam. Bldg Room # Web Cam Bldg Room # Web Cam 9101 9 101 Yes 29 200 No 29200 29 200 No 15456 15 456 Yes 11101 11 101 Yes Bldg Room # Web Cam 30200 30 200 Yes 9 101 Yes 17456 17 456 No 5224 5 224 No Bldg Room # Web Cam 15 456 Yes

has a web cam. VLOOKUP INDEX(MATCH()) INDEX(MATCH(MATCH())

Create a solution so that the user can select a room and bldg to lookup whether or not the room has a web cam. Unique Identifier Bldg Room # Web Cam Bldg Room # 9-101 9 101 Yes 15 456 29-200 29 200 No 15-456 15 456 Yes 11-101 11 101 Yes 30-200 30 200 Yes 17-456 17 456 No 5-224 5 224 No

m has a web cam. Web Cam Yes Web Cam Yes Web Cam Yes

Create a formula solution that will return multiple Server Names when an Event is selected in cell F6. Server Name Event Status Event Name ZZR45678A EVENT-1 Complete EVENT- ZZR45678A EVENT-2 Review BBT5643BG EVENT-1 Complete Count BBT5643BG EVENT-2 Not Yet Reviewed 3 BBT5643BG EVENT-3 Not Yet Reviewed N-987-HQ5SSD EVENT-1 Incomplete Events N-987-HQ5SSD EVENT-2 1/2 Stage Complete ZZR45678A BBT5643BG N-987-HQ5SSD

lected in cell F6. Events EVENT- EVENT- EVENT-