Excel 2016 Class 11: Lookup Functions & Formulas, Exercises of MS Microsoft Excel skills

Download Excel 2016 Class 11: Lookup Functions & Formulas and more Exercises MS Microsoft Excel skills in PDF only on Docsity!

Highline Excel 2016 Class 11: Lookup Functions & Formulas, Comprehensive Lessons VLOOKUP & More Topics: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Highline Excel 2016 Class 11: Lookup Functions & Formulas, Comprehensive Lessons VLOOKUP & More Data Validation List. VLOOKUP, Exact Match. IF & ISBLANK functions. Goal: Create Invoice. HLOOKUP with Exact Match Lookup. Goal: Lookup Tyrone's Phone Number. Horizontal Lookup is rare. VLOOKUP with Approximate Match Lookup. Goal: Lookup Pipe size. VLOOKUP with Approximate Match Lookup. Goal: Lookup Commission Paid. VLOOKUP with Approximate Match Lookup. Cell Reference for Column Index Number. Goal: Get Rating and Commission. VLOOKUP and MATCH functions with Exact Match Lookup. MATCH for Column Index Number. Goal: Retrieve employee dat VLOOKUP & ROWS or COLUMNS with Exact Match Lookup. ROWS or COLUMNS for Column Index Number. Goal: Retrieve r Multiple VLOOKUP functions. Goal: Calculate taxes from complex tax table. How to use VLOOKUP, LEFT and SEARCH to do a "Partial Text Lookup. Goal: Lookup Product Price. Also: Look at Data Misma INDEX and MATCH functions to do a "Lookup Left". INDEX array argument is a column. Goal: Given a flight range, lookup bo INDEX and MATCH to find Vendor for Low Bid. INDEX array argument is a row. Goal: lookup vendor name for lowest bid. INDEX and MATCH functions to do a Two-Way Lookup. INDEX array argument is a table. Goal: Do Two-Way Lookup to find INDEX and MATCH to lookup a whole column or row. INDEX array argument is a table. Goal: Lookup: 1) whole column of v How to use VLOOKUP & CHOOSE to do a lookup to multiple tables. Goal: Lookup Commission rate from Multiple Lookup ta LOOKUP with lookup_value and array arguments. Approximate Match. Goal: Lookup Commission and Rating. LOOKUP with lookup_value and lookup_vector and result_vector arguments. Approximate Match. Goal: Lookup Last Date a LOOKUP with lookup_value and lookup_vector and result_vector arguments. Approximate Match. Goal: "Lookup Adding" t INDEX and ROWS to Flip Grade Table. Goal: Flip a Grade Table. Use MATCH to Compare Two Lists. Goal: Is an item in List 1 also in List 2? Use MATCH to Compare Two Lists. Goal: Is an item in List 1 NOT in List 2?

Boomerang Part Number Flight Range (meters) Bellen 1000-165-B100 25 Carlota 1001-540-C101 20 Majestic Beaut 1002-394-M102 35 Quad 1003-307-Q103 20 Sunshine 1004-848-S104 30 Sunset 1005-155-S105 40 Tri-Fly 1006-552-T106 1 Outdoor Tri-Fly 1007-634-O107 5 Invoice Example: Product Units Price Carlota 5 28. Sunshine 2 18. Quad 10 35. Subtotal Category Tina Gigi Hire Date 1/4/2005 3/28/ Salary $93,976.00 $40,233. Office Number 302 348 Phone (206) 311-2567 (253) 577- Employee: Chin Phone: (206) 582-3391 (206) 582- Square footage of Lot Pipe diameter 0 6'' 500 8'' 1,000 10'' 5,000 16'' 10,000 25'' 25,000 35'' Example 1: Data Validation List. VLOOKUP, Exact Match. IF & ISBLANK functions. Goal: Create Invoice. Example 2: HLOOKUP with Exact Match Lookup. Goal: Lookup Tyrone's Phone Number. Horizontal Lookup is rare. Example 3: VLOOKUP with Approximate Match Lookup. Goal: Lookup Pipe size.

Square footage of Lot 2, Pipe diameter (inches) 10 Sales Amount Rating Commission Paid $0.00 Sub Par $0. $1,000.00 Par $20. $2,500.00 Above Par $100. $7,000.00 Very Good $250. $10,000.00 Excellent $700. If Jo had sales of $7,000 what is her commission pay? Name Sales Commission Paid Jo $6,999.99 100 2 Employee Sales Rating Sioux $7,598.00 Very Good Kim $68.00 Sub Par Gigi $15,980.00 Excellent Franny $2,499.99 Par ID Last First 880-10000 Leff Julianne 880-10001 Piano Milagros 880-10002 Coller Kathrine 880-10003 Stackpole Lonnie 880-10004 Lintz Kurt 880-10005 Dudgeon Penelope 880-10006 Hughs Pearlie MATCH function is a lookup function. MATCH function returns the relative position of an item in a list. This method is dynamic: 1) columns can be in any order and 2) If you change input formula updates. ID First Last Example 4: VLOOKUP with Approximate Match Lookup. Goal: Lookup Commission Paid. Example 5: VLOOKUP with Approximate Match Lookup. Cell Reference for Column Index Number. Goal: Get Rating Example 6: VLOOKUP and MATCH functions with Exact Match Lookup. MATCH for Column Index Number. Goal: Re

INDEX function: Can do many looks that VLOOKUP cannot Product Price Description V Range $12.95 Flying Range is 10 Quad $39.95 Flying Range is 20 Weighted Sunset $40.00 Flying Range is 50 Weighted MB $45.00 Flying Range is 60 S Rang $65.00 Flying Range is 70 Long D $69.00 Flying Range is 80 Long D Squared $100.00 Flying Range is 85 Dan's Special $110.00 Flying Range is 110 Manu Magic $165.00 Flying Range is 160 1-Way Lookup Vertical: Lookup Column to Left Description Product Flying Range is 70 S Rang S Rang Venders and bids: Crank'ys Bay Air Compressor R Us $38.99 $48.60 $43. $57.68 $31.80 $52. $53.32 $32.64 $37. $35.20 $40.55 $32. $56.72 $47.16 $36. $47.91 $35.08 $51. $34.81 $35.11 $48. $42.25 $35.76 $58. $40.14 $42.31 $37. $36.48 $40.79 $53. $38.57 $40.06 $54. $52.66 $43.61 $59. Example 10: INDEX and MATCH functions to do a "Lookup Left". INDEX array argument is a column. Goal: Given a fl array argument = values that you want to retrieve. array can be a table, a column, or a row. row_num argument = tells INDEX what row to look at to retrieve a value. If you put 0 or omitted, it will get "all the column_num argument tells INDEX what column to look at to retrieve a value. If you put 0 or omitted, it will get "al Use INDEX as a substitute for VLOOKUP when the value you want to return is to the left of the lookup column. Example 11: INDEX and MATCH to find Vendor for Low Bid. INDEX array argument is a row. Goal: lookup vendor na Example 12: INDEX and MATCH functions to do a Two-Way Lookup. INDEX array argument is a table. Goal: Do Two

2-Way lookup Discount Table PartID/Qty 1 5 Part1 13.00% 15.00% Part2 14.00% 16.00% Part3 15.00% 17.00% Part4 16.00% 18.00% Part5 17.00% 19.00% Part6 18.00% 20.00% PartID Part3 3 Qty 18 2 Discount 17.00% Discount Lookup whole row or column Use INDEX, MATCH and SUM to retrieve a whole column of values and add them 0 (zero) or empty in row_num argument will return a whole column of values (“all rows” or “entire c Month to add Mar Total Mar 18 Jan Feb Mar 1 4 4 3 4 12 1 4 2 Use INDEX, MATCH and SUM to retrieve a whole row of values and add them 0 (zero) or empty in column_num argument will return a whole row of values (“all columns” or “enti Month to add Mar Total Mar 4 Jan 1 3 Feb 4 4 Mar 4 12 Apr 5 1 CHOOSE functions allows you to lookup things like different lookup tables. Example 13: INDEX and MATCH to lookup a whole column or row. INDEX array argument is a table. Goal: Lookup: Example 14: How to use VLOOKUP & CHOOSE to do a lookup to multiple tables. Goal: Lookup Commission rate from =CHOOSE( index_num , value1 , value2 , …) index_num is a number like 1, 2, 3, 4

Reason we use LOOKUP rather than VLOOKUP: Fewer Arguments to Enter. If Jo had sales of $1,500 what is her commission pay? Name Sales Commission Paid Jo $1,500.00 20 Lookup 20 Reason we use LOOKUP: LOOKUP can do Lookup Array Calculations without Ctrl + Shift + Enter: 3455 240th St., Des Monies 1254 10th St., Seattle Date Site Visited 5/1/2016 3455 240th St., Des Monies 5/1/2016 1254 10th St., Seattle 1254 10th St., Seattle 5/2/2016 478 Birch St., Tukwila 5/5/ 5/5/2016 3455 240th St., Des Monies 1254 10th St., Seattle Example 15: LOOKUP with lookup_value and array arguments. Approximate Match. Goal: Lookup Commission and Example 16: LOOKUP with lookup_value and lookup_vector and result_vector arguments. Approximate Match. Go Get Last Site based on Last Date Get Last Site based on Last Date

Price $26. $28. $31. $35. $18. $20. $4. $8. Total Price Price 144.75 28.95 28. 37.9 18.95 18. 359.5 35.95 35.

Chin Tyrone Bobi 6/11/2012 5/24/2010 2/2/ $36,762.00 $89,589.00 $52,319. 383 230 255 (206) 582-3391 (253) 561-5768 (253) 598- 07:08 minute mark explains how Bianary Search Works: tions. Goal: Create Invoice. one Number. Horizontal Lookup is rare. Approximate Match & Sorting To Reduce Exact Match Calculation Time VLOOKUP WEEK # https://www.youtube.com/watch?v=wojchNbrCqA

[email protected] tells you how many columns. Expandable range. e field names for MATCH. Stackpole Lonnie [email protected] 253-764- Rate Rule 0% $0.00 Zero Tax 10% $0.00^ 10% of excess over $1, 15% $72.50^ $72.50 + 15% of excess over $2, 25% $712.40^ $712.40 + 25% of excess over $6, 28% $1,597.40^ $1,597.40 + 28% of excess over $9, 33% $3,895.08^ $3,895.08 + 33% of excess over $18, 35% $8,407.83^ $8,407.83 + 35% of excess over $31, =VLOOKUP($C$115,$C$107:$G$113,4) =VLOOKUP(C115,C107:G113,3) =VLOOKUP($C$115,C107:G1=VLOOKUP($C$115,C107:G113,1) =C115-C =C116+ROUND(C119C117,2) =VLOOKUP($C$115,$C$107:$G$113,4)+ROUND((C115-VLOOKUP($C$115,$C$107:$G$113,1))VLOOKUP($C$115,$C$107:$G$ ID Price ID Bellen $26.00 234 Carlota $23.00 345 Quad $36.00 765 WS or COLUMNS for Column Index Number. Goal: Retrieve record from Employee Table. ex tax table. Tax from Previous brackets ookup. Goal: Lookup Product Price.

PRO

PRO

PRO

PRO

PRO

PRO

PRO 1

PRO 1

PRO 1

PRO 1

Mech-Aid Han's Shop Low Bid Vendor for Low Bid $40.08 $47.92 $38.99 Crank'ys Crank'ys $31.42 $55.19 $31.42 Mech-Aid Mech-Aid $48.29 $41.59 $32.64 Bay Air Bay Air $36.81 $41.14 $32.65 Compressor R Us Compressor R Us $49.56 $39.25 $36.42 Compressor R Us Compressor R Us $49.84 $42.12 $35.08 Bay Air Bay Air $33.32 $37.83 $33.32 Mech-Aid Mech-Aid $46.28 $40.53 $35.76 Bay Air Bay Air $59.97 $42.57 $37.62 Compressor R Us Compressor R Us $51.01 $51.24 $36.48 Crank'ys Crank'ys $39.70 $54.73 $38.57 Crank'ys Crank'ys $34.61 $52.65 $34.61 Mech-Aid Mech-Aid ay argument is a column. Goal: Given a flight range, lookup boomerang name in first column. olumn, or a row. you put 0 or omitted, it will get "all the rows" (whole column). lue. If you put 0 or omitted, it will get "all the columns" (whole row). gument is a row. Goal: lookup vendor name for lowest bid. array argument is a table. Goal: Do Two-Way Lookup to find Discount.

he VLOOKUP function ABC Units Sold Commission Rate Commission Rate 0 1.00% 4.00% 100 2.00% 1.00% 200 4.00% 4.00% 500 6.00% 4.00% 2.00% EDR Units Sold Commission Rate 0 1.00% Commission Rate 200 2.00% 4.00% 300 4.00% 1.00% 400 6.00% 4.00% 4.00% EDS 2.00% Units Sold Commission Rate 0 2.00% 300 3.00% 500 4.00% 750 5.00% de or the same width and height. ange with values you would like to retrieve (result_vector). EK Video #5)

Rating Par Par out Ctrl + Shift + Enter: te Match. Goal: Lookup Commission and Rating. ctor arguments. Approximate Match. Goal: Lookup Last Date and find site.

OOKUP($C$115,$C$107:$G$113,3),2)

Price $26. $23. $36.

Technically there is a way to do it with VLOOKUP, it's just harder... S Rang Compressor R Us Compressor R Us Compressor R Us