Advanced Excel
Module 2 Lecture
Module 2 –lookup function and data Table management
Outline
Part 1 Lookup Functions
- Vlookup
• Exact lookup
• Approximate lookup
- HLookup
- XLookup
Part 2 Sorting and Filtering Using Tables
- Sort by one or more columns
- Filter by color
- Filter with conditions
- Text Filter
- Format table as Table and rename
Part 1 Lookup functions
File: Part 1 - Basic lookup functions.xlsx
VLOOKUP is a powerful Excel function that allows you to search for a value in the first column of a table and return a value in the same row from a specified column.
Vlookup will lookup the Lookup_value in the 1st column of the Table_array, and try to find the value that matches with the Lookup_value , once found, return the cells content in the
col_index_num with an approximate or an exact match (Range_lookup)
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
Lookup_Value:
• Is the value to be found Table_Array:
• Is a table of data in which data is retrieved
Table_array can be a reference to a range or to a table name Col_Index_num:
• Is the column number in table_array from which the matching value should be returned
e.g. the first column of values in the table is column 1 Range_lookup:
• Is a logical value: to find the closest match in the first column of the table (sorted in ascending order) = TRUE or 1 or to find an exact match = False or 0
Worksheet: Vlookup (Exact match)
Task 1: Retrieve Name and Price information corresponding to the ID using vlookup
o C17: =VLOOKUP(C15,A3:C12,2,FALSE)
o C18: =VLOOKUP(C15,A3:C12,3,0)
Worksheet: Vlookup (Approximate match)
Task 2: Find Discount rate (D4:D13) for each price from the discount table (H3:I8) o D4: =VLOOKUP(C4,$H$4:$I$8,2,TRUE)
Note:
• there is no exact match of the price to the discount rate table.
- Example: For Price = 675.18, excel will go through the discount table and try to find the interval that contains this value. First, it tries to find the step value (600) that is lower than the Price, and the next step value (900) is higher than the Price, then
returns the corresponding discount rate within this interval, that is 10%.
• If the price is -100$, the output will be #N/A since Excel cannot find a step valueless than - 100$.
• Remember to lock the table_array with $ sign
or
You may also use a named table, Table_Discount for H5:I8
- Method 1: Format an excel Table
Select any cell within the table, or range of cells you want to format as a table
On the Home tab, click Format as Table
Keep one cell active on the table
Table tab > change the name in Table Name field
- Method 2: Define name
Select the cell, range of cells that you want to name
On the Formulas tab, click Define name
Type the name in Name field
Task 3: Retrieve Grade (D17:D27) for each mark from grade table (H17:I27)
o Name grade table H17:I27 as TableGradeScale
o D17: =VLOOKUP(C17,TableGradeScale,2,TRUE)
Note: When implementing an approximate match, you need to define your searching table_array in ascending order!
If the grade scale table is in descending order, you'll get the following:
because excel is unable to search for approximate values in any order other than ascending order
Now the table is horizontal. We can use Hlookup
HLOOKUP(lookup_value, table_array, row_index_num,[range_lookup]) Task 4: Retrieve Name and Price information given the ID using Hlookup
o C7: =HLOOKUP(C6,$B$2:$K$4,2,FALSE)
o C8: =HLOOKUP(C6,$B$2:$K$4,3,FALSE)
Worksheet: Xlookup
X lookup will research the Lookup_value in the range of the Lookup_array, and try to find the value that matches with the Lookup_value , once found, return the cells content within the
Return_array when the Lookup_value is not found, If_not_found value is returned and the
research will be done with a specific Match_mode method and with a specific Search_mode
XLOOKUP(lookup_value, Lookup_array, Return_array, If_not_found, Match_mode) Lookup_value:
• Is the value to search for Lookup_array:
• Is the array or range to search Return_array:
• Is the array or range to search If_not_found:
• Is returned if no match is found Match_mode:
• Specify how to match Looup_value against the values in Lookup_array 0: exact match, -1: exact match or next smaller, 1: exact or next larger, 2: wildcard character match)
Search_mode:
• Specify the search mode to use
1: search first to last, -1: search last to first
Benefits of using Xlookup:
It can use a lot of different match mode
It can search for both horizontal and vertical data
It can perform. a reverse search
It can return entire rows and columns of data instead of a single value
It can include the "if not found" argument
Task 5: Retrieve Name and Country information given the ID using Xlookup
o Method 1: using range
B3: =XLOOKUP(A3;C6:C15;A6:B15;"ID not found";0;1)
o Method 2: using table array
Assuming that the cell range A5:D15 has been formatted as a table with the name
Table_Employee
B3: =XLOOKUP(A3;Table_Employee[Emp ID];Table_Employee[[Employee Name]:[Country]];"ID not found";0;1)
Part 2 Sorting, filtering, Using Tables
File: Part 2 - Sorting, filtering, tables.xlsx
Task 1: Sort by CustomerID in descending order
o Method 1:
• Select the entire table (Ctrl+A) > Data tab> in Sort & Filter Click on Sort
• Select CustomerID, on what you want to sort and the order
Note: Excel will automatically identify if there is a header.
be sorted.
o Method 2:
• Format range A1:H202 as a table:
• Select one cell in the table
• In Home tab, click Format as table button
you get automatically into the header row the filter button you can sort the column with the order you want
• Click filter button of the column CustomerID
• Then select Sort Largest to Smaller
Task 2: Sort table with two columns (levels): LastName ascending, FirstName descending
Note: you must use Sort dialog box (see Task1, Method1) because when you use twice the Filter button Excel will retain only the last sort you did
Task 3: Sort or Filter by color
o Click on the ‘Filter’ button of the column CustomerID
o Select Sort by color or Filter by color
Note: The rows are not in consecutive numbers. The funnel icon in the filter button shows that filtering has been done in that column.
Task 4: Clear Filter
o Method 1:
• Click the funnel icon of the column for which you want to clear the filter
• Select Clear Filter
This method clears only the filter on this column
o Method 2:
• Data tab> in Sort & Filter Click on Clear 限clear This method clear all columns filters
Important: it is possible to clear a filter BUT it is not possible to clear a sort. Task 5: Number Filter with CustomerID greater than 100
o Click on the ‘Filter’ button of the column CustomerID
o Select Number Filters and Greater Than …
o Then type 100
Task 6: Text Filter all customers living in New York
o Click on the ‘Filter’ button of the column City
o Type New York in the Searchbox
Task 7: Text Filter all customers with LastName end with ‘on’
o Click on the ‘Filter’ button of the column LastName
o Select Text Filters and Ends with …
o Type on
Task 8: Text Filter all customers with Address contains 'road'
o Click on the ‘Filter’ button of the column Address
o Type road in Searchbox
Task 9: Text Filter all customers with 'a' as second letter of LastName
o Click on the ‘Filter’ button of the column LastName
o Select Text Filters and Begins with …
o Type ?a
Note: here, ? is what we call a wildcard, it represent any single character
Task 10: Format the ‘Customers table’ as Table If it's not already done:
o Select one cell into the table
Warning: DO NOT select the whole worksheet, the table range is A1:I202
o Select the table region > Ribbon ‘ Home’ > ‘Styles’ > ‘Format as Table’
Now if you click on any cell, a new ribbon ‘Table Design’ will appear. Try the following operation:
• Rename the Table as ‘Table_Customer’
You'll find Table name on the left side of the Table Design tab
• Add a new column 'FullName' at the end of the table Excel resizes automatically the table
• In 'FullName' column, Create a formula to concatenate FirstName and LastName columns, you'll add a space as separator
Note: when you click into the cell, Excel table recognizes automatically column names you should get the following formula: =[@FirstName]&" "&[@LastName]
• Add to the bottom of the table a total row to count the number of customers
Tips: Check Total row option in Table Design tab, and you get automatically the count at the bottom of the last column
Note: you could change the calculation using the drop-down list at the right side of the cell
Worksheets ‘Products’, ‘Orders’ and ‘Orders products list’
Task 9: Change Table design for worksheets ‘Products’, ‘Orders’ and ‘Orders’ products list’, and name them ‘TableProducts’, ‘TableOrders’ and ‘TableOrderDetails’ respectively.
Worksheet ‘Orders products list’
Task 10: Add new column 'Price' after the last column Note that the table is automatically resized,
and calculate the 'Price' = ‘Price per Unit’ * ‘Quantity’
Note that the formula takes into account the table column names,
[@[Price per Unit]]*[@Quantity] and not the cell references when clicking into the cell, C2*D2 You may also notice that the formula is automatically copied to the bottom of the table
Task 11: Add new column 'Final Price' after the last column and calculate as ‘Price’ – ‘Price’ * ‘Discount’
Task 12: In Ribbon ‘Table Design’, Remove duplicates with the same ProductName
Task 13: In Ribbon ‘Table Design’, insert Slicer of ProductName and select all 'Chocolate' products