際際滷

際際滷Share a Scribd company logo

1 Day Advance Excel Workshop (Vlookup) in Mumbai

F
Faiyaz Khairaz

We @ it corporate training & compufield offer a 1 day Extensive Training in VlookUP. In this workshop we cover over 15 different scenarios and opportunities to use VlookUP. www.it-corporate-training.com/vlookup_training_excel_mumbai.html From Using Vlookup for exact matches, approximations, using text strings, match, index, nested vlookup and many more such examples. For Example, if you have imported "A" goods from China 50 times, then what was the maximum cost you have paid, and the last 5 times you imported, what was the cost. All examples have been inspired from the Training's which we conduct for corporates, hence we have bundled this as 1 workshop.

1 of 17
Downloaded 67 times
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 An Extensive 1 Day (8 Hours) workshop for Sales & Marketing Team on the Variations of VlookUP -足By Faiyaz M Khairaz +91 9819006132
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Beyond the Regular VLookUP VlookUP is one of the most powerful functions in Excel Most Frequently used function by Sales / Marketing Teams Used extensively to create Invoices, Pending Payments, Compare Sales Reports
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Basics of VlookUP We would initially cover the Basics of VlookUP. Di鍖erence between Exact & Approximate Matches . Requirements within Data, to use VlookUP. Limitations of VLookUP.
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Example 1 Scope Of Chapter: Given, Sample data, and a Form . The User has to Enter the Employee Code, and Automatically the remaining details need to appear in the form. For Example, if the user enters 89 in the Employee Code, Tejal Patel Should appear under the name and remaining details should appear accordingly
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Example 2: VlookUP with Approximate Match Scope Of Chapter: VLookUP with Approximation A user would Enter the Salary Amount, and the Tax Amount should be Calculated Automatically. For Example, Salary Rs 23550 should attract TAX 7.20% Here, the LookUP Value is an Approximate Match
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Step 1: Given the State, we need to 鍖nd the Region Step 2: After Finding the region, depending on the number of dependents, we need to search the TAX Rate Example 3: (Nested VlookUP) Number of Dependents
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Match & Index Limitation of VLookUP, the left most column needs to be the LookUP Value. Suppose the VlookUP Value (The Value which you need to 鍖nd) is not the Left Most Column of your Selection VlookUP would not work. Solution, Match + Index. We take multiple examples where the Left Column is not the LookUP Value and show examples for Match + Index
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Given the Social Security Number, Find the Name of the Person Example 4: (Match & Index) Data Table
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Question 1. Who made more sales -足 Jamie or Jackie? 2. What is the sale per customer for Jagjit? 3. What is the pro鍖t % for person in C20? Example 5: HLookUP (Horizontal LookUP)
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Using VlookUP with Tables Advantages of Using VlookUP with Tables If Table (data) is modi鍖ed (An extra column is added) then too the column index number would not get modi鍖ed Instead of mentioning the Column Number in vlookUP, how can we use the Column Header Name, which makes it easy for us to remember
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Questions 1. Sales for the person whose names starts with Jac 2. Sales for the person whose name as 8 characters 3. Sales for the person whose name ends with son 4. Customers for person whose name starts with ja and ends with am Questions 1. How many sales for the person whose name is Josh 2. Who made more sales -足 person ending with ph or starting with je? 3. What is the netsales for the person who had 6 customers? Example 6: Wild Card Search Using VlookUP Scope of Chapter: Extending the use of VlookUP with Wild Card Searches.
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 CreaBng Invoices Example 7: Creating Invoices Using VlookUP & Validation Scope of Chapter: Having Data in a separate sheet, we create a new sheet in which the user selects the Invoice Number and checks the Payment Status Invoice Numbers selected from a Drop Down Status of Payment Appears Accordingly
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Questions 1. Which person made sales = 1088 2. Who made maximum sales? 3. Who sold to minimum number of customers? 4. What is sale per customer for the person who has lowest pro鍖t ratio? 1. Who sold second highest? 2. What is the Pro鍖t Ratio rank of person who sold second highest? 3. What is di鍖erence in sale per customer between the highest selling & lowest selling sales persons? Example 8: Using Max, Min, and Functions with VlookUP Scope of Chapter: For example, the lookUP value would not be a static value, it would be based on a formula, for example, the person having highest sales, would be the lookUP value. Suppose we change a cell, then automatically the lookUP value should change.
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 What we need? -足 Pro鍖t % Salesman= Lawrence Region= West Month= Feb-足13 Product= Rapidzoo What we need Total sales in Jan,Feb,Mar 2013 Salesman= Lawrence Region= West Example 9: VlookUP using Multiple LookUP Values Scope of Chapter: Using VlookUP with Multiple Conditions For Example, We need the total sales (Jan+Feb+March+April) for salesman, Lawrence & Region: West We select Sales Man & Region from List, the user can change the values and dynamically get the result.
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Sales Person Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Joseph $ 22 22 24 25 26 28 30 32 33 33 36 36 John 10 10 11 11 12 12 13 13 14 15 15 15 Josh 26 26 27 27 27 27 29 30 30 32 32 34 Jamie 18 19 20 20 20 21 22 23 23 24 25 25 Jackie 19 20 22 24 25 25 25 27 29 30 30 31 Johnson 16 17 18 18 19 20 22 24 25 26 27 28 Jonathan 15 16 17 17 18 18 18 19 19 20 20 22 Jagjit 10 11 11 11 12 13 13 14 14 14 15 15 Jairam 21 21 21 23 24 24 25 26 27 29 30 32 Jessy 25 26 27 28 29 29 29 30 31 34 37 40 Javed 22 24 25 26 28 28 29 31 34 35 35 35 Jimmy 23 24 24 24 25 26 27 28 30 33 35 37 Juno 19 20 20 21 22 24 24 26 27 27 28 30 Questions 1. Total Sales in Months Jan, Feb & Mar for the person Joseph 2. Maximum sales per month of Josh 3. Average monthly sale for Jonathan 4.What is the total commission made by Jackie? Question 2: Complete the below Table with total Quarterly sales John Josh Jamie Quarter 1 (APR -足 JUN) Quarter 2 (JUL -足 SEP) Quarter 3 (OCT -足 DEC) Quarter 4 (JAN -足 MAR) Practice Example
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Sales Person Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Joseph $ 22 22 24 25 26 28 30 32 33 33 36 36 John 10 10 11 11 12 12 13 13 14 15 15 15 Josh 26 26 27 27 27 27 29 30 30 32 32 34 Jamie 18 19 20 20 20 21 22 23 23 24 25 25 Jackie 19 20 22 24 25 25 25 27 29 30 30 31 Johnson 16 17 18 18 19 20 22 24 25 26 27 28 Jonathan 15 16 17 17 18 18 18 19 19 20 20 22 Jagjit 10 11 11 11 12 13 13 14 14 14 15 15 Jairam 21 21 21 23 24 24 25 26 27 29 30 32 Jessy 25 26 27 28 29 29 29 30 31 34 37 40 Javed 22 24 25 26 28 28 29 31 34 35 35 35 Jimmy 23 24 24 24 25 26 27 28 30 33 35 37 Juno 19 20 20 21 22 24 24 26 27 27 28 30 Please Select: Sales Person Johnson Month Sep Practice Example
IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Actual Data Sales Person No. Customers Net Sales Pro鍖t / Loss Joseph 8 1,592 563 John 8 1,088 397 Josh 8 1,680 753 Jamie 9 2,133 923 Joseph 10 1,610 579 John 10 1,540 570 Josh 7 1,316 428 Jamie 7 1,799 709 Joseph 8 1,624 621 John 6 726 236 Josh 9 2,277 966 Jamie 6 714 221 John 9 2,682 1,023 Sales in Each Occurrence Sales Person Sales 1 Sales 2 Sales 3 Sales 4 Joseph John Jackie Example 10: Having Multiple LookUP Values Scope of Chapter: In this Data, Joseph has made sales multiple times. Using VlookUP, suppose we want the 2nd Iterative of Josephs Net Sales. (using VlookUP)

More Related Content

1 Day Advance Excel Workshop (Vlookup) in Mumbai

  • 1. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 An Extensive 1 Day (8 Hours) workshop for Sales & Marketing Team on the Variations of VlookUP -足By Faiyaz M Khairaz +91 9819006132
  • 2. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Beyond the Regular VLookUP VlookUP is one of the most powerful functions in Excel Most Frequently used function by Sales / Marketing Teams Used extensively to create Invoices, Pending Payments, Compare Sales Reports
  • 3. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Basics of VlookUP We would initially cover the Basics of VlookUP. Di鍖erence between Exact & Approximate Matches . Requirements within Data, to use VlookUP. Limitations of VLookUP.
  • 4. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Example 1 Scope Of Chapter: Given, Sample data, and a Form . The User has to Enter the Employee Code, and Automatically the remaining details need to appear in the form. For Example, if the user enters 89 in the Employee Code, Tejal Patel Should appear under the name and remaining details should appear accordingly
  • 5. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Example 2: VlookUP with Approximate Match Scope Of Chapter: VLookUP with Approximation A user would Enter the Salary Amount, and the Tax Amount should be Calculated Automatically. For Example, Salary Rs 23550 should attract TAX 7.20% Here, the LookUP Value is an Approximate Match
  • 6. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Step 1: Given the State, we need to 鍖nd the Region Step 2: After Finding the region, depending on the number of dependents, we need to search the TAX Rate Example 3: (Nested VlookUP) Number of Dependents
  • 7. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Match & Index Limitation of VLookUP, the left most column needs to be the LookUP Value. Suppose the VlookUP Value (The Value which you need to 鍖nd) is not the Left Most Column of your Selection VlookUP would not work. Solution, Match + Index. We take multiple examples where the Left Column is not the LookUP Value and show examples for Match + Index
  • 8. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Given the Social Security Number, Find the Name of the Person Example 4: (Match & Index) Data Table
  • 9. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Question 1. Who made more sales -足 Jamie or Jackie? 2. What is the sale per customer for Jagjit? 3. What is the pro鍖t % for person in C20? Example 5: HLookUP (Horizontal LookUP)
  • 10. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Using VlookUP with Tables Advantages of Using VlookUP with Tables If Table (data) is modi鍖ed (An extra column is added) then too the column index number would not get modi鍖ed Instead of mentioning the Column Number in vlookUP, how can we use the Column Header Name, which makes it easy for us to remember
  • 11. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Questions 1. Sales for the person whose names starts with Jac 2. Sales for the person whose name as 8 characters 3. Sales for the person whose name ends with son 4. Customers for person whose name starts with ja and ends with am Questions 1. How many sales for the person whose name is Josh 2. Who made more sales -足 person ending with ph or starting with je? 3. What is the netsales for the person who had 6 customers? Example 6: Wild Card Search Using VlookUP Scope of Chapter: Extending the use of VlookUP with Wild Card Searches.
  • 12. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 CreaBng Invoices Example 7: Creating Invoices Using VlookUP & Validation Scope of Chapter: Having Data in a separate sheet, we create a new sheet in which the user selects the Invoice Number and checks the Payment Status Invoice Numbers selected from a Drop Down Status of Payment Appears Accordingly
  • 13. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Questions 1. Which person made sales = 1088 2. Who made maximum sales? 3. Who sold to minimum number of customers? 4. What is sale per customer for the person who has lowest pro鍖t ratio? 1. Who sold second highest? 2. What is the Pro鍖t Ratio rank of person who sold second highest? 3. What is di鍖erence in sale per customer between the highest selling & lowest selling sales persons? Example 8: Using Max, Min, and Functions with VlookUP Scope of Chapter: For example, the lookUP value would not be a static value, it would be based on a formula, for example, the person having highest sales, would be the lookUP value. Suppose we change a cell, then automatically the lookUP value should change.
  • 14. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 What we need? -足 Pro鍖t % Salesman= Lawrence Region= West Month= Feb-足13 Product= Rapidzoo What we need Total sales in Jan,Feb,Mar 2013 Salesman= Lawrence Region= West Example 9: VlookUP using Multiple LookUP Values Scope of Chapter: Using VlookUP with Multiple Conditions For Example, We need the total sales (Jan+Feb+March+April) for salesman, Lawrence & Region: West We select Sales Man & Region from List, the user can change the values and dynamically get the result.
  • 15. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Sales Person Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Joseph $ 22 22 24 25 26 28 30 32 33 33 36 36 John 10 10 11 11 12 12 13 13 14 15 15 15 Josh 26 26 27 27 27 27 29 30 30 32 32 34 Jamie 18 19 20 20 20 21 22 23 23 24 25 25 Jackie 19 20 22 24 25 25 25 27 29 30 30 31 Johnson 16 17 18 18 19 20 22 24 25 26 27 28 Jonathan 15 16 17 17 18 18 18 19 19 20 20 22 Jagjit 10 11 11 11 12 13 13 14 14 14 15 15 Jairam 21 21 21 23 24 24 25 26 27 29 30 32 Jessy 25 26 27 28 29 29 29 30 31 34 37 40 Javed 22 24 25 26 28 28 29 31 34 35 35 35 Jimmy 23 24 24 24 25 26 27 28 30 33 35 37 Juno 19 20 20 21 22 24 24 26 27 27 28 30 Questions 1. Total Sales in Months Jan, Feb & Mar for the person Joseph 2. Maximum sales per month of Josh 3. Average monthly sale for Jonathan 4.What is the total commission made by Jackie? Question 2: Complete the below Table with total Quarterly sales John Josh Jamie Quarter 1 (APR -足 JUN) Quarter 2 (JUL -足 SEP) Quarter 3 (OCT -足 DEC) Quarter 4 (JAN -足 MAR) Practice Example
  • 16. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Sales Person Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Joseph $ 22 22 24 25 26 28 30 32 33 33 36 36 John 10 10 11 11 12 12 13 13 14 15 15 15 Josh 26 26 27 27 27 27 29 30 30 32 32 34 Jamie 18 19 20 20 20 21 22 23 23 24 25 25 Jackie 19 20 22 24 25 25 25 27 29 30 30 31 Johnson 16 17 18 18 19 20 22 24 25 26 27 28 Jonathan 15 16 17 17 18 18 18 19 19 20 20 22 Jagjit 10 11 11 11 12 13 13 14 14 14 15 15 Jairam 21 21 21 23 24 24 25 26 27 29 30 32 Jessy 25 26 27 28 29 29 29 30 31 34 37 40 Javed 22 24 25 26 28 28 29 31 34 35 35 35 Jimmy 23 24 24 24 25 26 27 28 30 33 35 37 Juno 19 20 20 21 22 24 24 26 27 27 28 30 Please Select: Sales Person Johnson Month Sep Practice Example
  • 17. IT Corporate Training (www.it-足corporate-足training.com) Faiyaz M Khairaz : +91 9819006132 Actual Data Sales Person No. Customers Net Sales Pro鍖t / Loss Joseph 8 1,592 563 John 8 1,088 397 Josh 8 1,680 753 Jamie 9 2,133 923 Joseph 10 1,610 579 John 10 1,540 570 Josh 7 1,316 428 Jamie 7 1,799 709 Joseph 8 1,624 621 John 6 726 236 Josh 9 2,277 966 Jamie 6 714 221 John 9 2,682 1,023 Sales in Each Occurrence Sales Person Sales 1 Sales 2 Sales 3 Sales 4 Joseph John Jackie Example 10: Having Multiple LookUP Values Scope of Chapter: In this Data, Joseph has made sales multiple times. Using VlookUP, suppose we want the 2nd Iterative of Josephs Net Sales. (using VlookUP)