Microsoft Excel Lookup Tables PDF

CST8118 Computer Essentials Lookup Tables  Week 10 Lookup Tables What is a Lookup Table? What MS Excel functions are used for lookup tables? LOOKUP, VLOOKUP, XLOOKUP XLOOKUP Syntax Overview Example 1: Determining letter grade Example 2: Finding web colors...

CST8118 Computer Essentials Lookup Tables  Week 10 Lookup Tables What is a Lookup Table? What MS Excel functions are used for lookup tables? LOOKUP, VLOOKUP, XLOOKUP XLOOKUP Syntax Overview Example 1: Determining letter grade Example 2: Finding web colors Caution When Using XLOOKUP and Autofill 2  Welcome This slide set overviews creating lookup tables using the MS Excel XLOOKUP function. 3  What is a Lookup Table? A lookup table is simply a table of data organized so that one (or more columns) can be matched against a search value, and a data item in a different column returned. Uses a phone book as a classic example of a lookup table. Using a persons name you can lookup their phone number. Lookup tables can be used for other tasks, for example determining your letter grade, looking up named web color codes using the colors hexadecimal or decimal codes. 4  What MS Excel functions are used for lookup tables? Answer: LOOKUP, VLOOKUP, XLOOKUP LOOKUP The oldest lookup function, can only search one row or one column. VLOOKUP Works like LOOKUP, except you can search multiple rows and columns. XLOOKUP Faster than LOOKUP and VLOOKUP. You may see LOOKUP and/or VLOOKUP in older spreadsheets ,, however you should use XLOOKUP if available in your version of MS Excel. 5  XLOOKUP Syntax Overview Notes are taken from =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) lookup_value a value or cell reference lookup_array the array or range of cells to search in return_array the array or range of cells containing possible match results (continues next slide) 6  XLOOKUP Syntax Overview (continued) =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) [if_not_found] Optional, message to return if not found, default is #N/A [match_mode] Optional, default is exact match, other options include exact match or next smallest item, exact match or next largest item, or a wildcard match. [search_mode] Optional, default is to start search at first item, other options are a reverse order search, as well as binary searches. Note that the binary searches require the lookup_array to be sorted. 7  Example 1: Determining Letter Grade (Part 1) See the MS Excel File: XLOOKUP Examples.xlsx This example follows from a similar example in. Figure 1.0 8  Example 1: Determining Letter Grade (Part 2) The image on the previous slide (Figure 1.0) There is a table consisting of 3 columns with headings A is Percent Grade, B is Letter Grade, C is Numeric Grade with the lower-boundaries for each letter grade listed in the percent grade column, e.g., where 90 and above is A+ the 100 (upper- boundary) was omitted. The cells with the lookup values range from 8 to 20. The user would enter a number for their course grade into the cell to the right of the Grade Earned cell, (Cell F8) then XLOOKUP functions are used to determine the letter grade and Numeric Grade. The functions were shown as plain-text in the spreadsheet, but this is not needed. Example function: =XLOOKUP(F8,A8:A20,B8:B20,,-1,1) (Function arguments are detailed on next slide) 9  Example 1: Determining Letter Grade (Part 3) With reference to Figure 1.0 =XLOOKUP(F8,A8:A20,B8:B20,,-1,1) F8 is the cell with the value to lookup A8:A20 is the range of cells with values to search B8:B20 is the range of cells with values to return if there is a match ,, is the argument for the text to return if there is no match, it was left blank, so we use the default return #N/A -1 specifies that the match mode should be exact, followed by next smallest item 1 specifies that the search mode is first to last, this could have been omitted as this is the default search mode 10  Example 2: Finding Web Colors (Part 1) Figure 2.0 The image above shows a lookup table on the left in columns A and B with cell ranges from 12 to 152. A user can enter either a named web color, to lookup the hex code, or enter a hex code to lookup the named web color in cells E12 and E15 respectively. Samples of the formulas were documented as plain-text in the worksheet, but this is not needed. 11  Example 2: Finding Web Colors (Part 2) When developing websites HTML color codes are used to change background and font colors for web pages. There are 17 standardized named colors, with up to 147 named colors recognized by web browsers. Web colors can be encoded using three hexadecimal values from 0 to 255, i.e., 0 to FF. The first value represents Red, the second Green, the third Blue, commonly referred to as RGB Values. Example #00FF00 would be zero red, FF green, and zero blue so the displayed color would be green. 12  Example 2: Finding Web Colors (Part 3) Named colors are in the range A12 to A152 Hexadecimal values are in range B12 to B152 Named color to lookup hex value is entered into E12 Hex value to lookup named color is entered into E15 XLOOKUP formulas are set up as: Hex Value lookup: =XLOOKUP(E12,A13:A152,B13:B152,"no match found") Named web color lookup: =XLOOKUP(E15,B13:B152,A13:A152,"no match found") 13  Caution When Using XLOOKUP and Autofill (1) When using XLOOKUP with Autofill be careful. The lookup_array and match_array cell ranges should likely be absolute, not relative. Please see “XLookup Caution with autofill.xlsx” Given the lookup table cells with numeric grades from 90 to 0 at intervals downwards in column F, and letter grades from A+ to F downwards, in column G in matching intervals. (See next slide) 14  Caution When Using XLOOKUP and Autofill (2) The letter grades using XLOOKUP with relative cell ranges are not correct. The letter grades using XLOOKUP with absolute cell ranges are correct. Autofill adjusted the relative cell ranges for the lookup value reference, and the lookup range, and match ranges as well. 15  Caution When Using XLOOKUP and Autofill (3) Cell C2 selected, relative ranges are correct =XLOOKUP(B2,F2:F14,G2:G14,"not found",-1,1) 16  Caution When Using XLOOKUP and Autofill (4) Cell C10 selected, relative ranges are incorrect due to autofill adjusting them =XLOOKUP(B2,F10:F24,G10:G24,"not found",-1,1) 17  Caution When Using XLOOKUP and Autofill (5) Absolute references for lookup ranges fixes this issue =XLOOKUP(B10,$F$2:$F$14,$G$2:$G$14,"not found",-1,1) 18  Conclusion In this lesson we reviewed: What is a Lookup Table? What MS Excel functions are used for lookup tables? LOOKUP, VLOOKUP, XLOOKUP XLOOKUP Syntax Overview Example 1: Determining letter grade Example 2: Finding web colors Caution When Using XLOOKUP and Autofill 19  References (Part 1) Michael Alexander and Dick Kusleika. 2022. Microsoft Excel 365 Bible. 1st Ed. John Wiley & Sons, Inc., Hoboken, New Jersey. Print ISBN: 978-1-119-83510-3. Ch 15. LOOKUP function. support.microsoft.com. https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-82 51-369d5e3864cb (Accessed Oct 18, 2023) XLOOKUP function. support.microsoft.com. https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-8 4f9-88eae8bf5929 (Accessed Oct 18, 2023) Michael Alexander and Dick Kusleika. 2022. Microsoft Excel 365 Bible. 1st Ed. 20 John Wiley & Sons, Inc., Hoboken, New Jersey. Print ISBN: 978-1-119-83510-3.  References (Part 2) Mrinal Bhattacharya. What are the HTML Color Names Supported by All Browsers? scaler.com https://www.scaler.com/topics/html-color-names/ (Accessed Oct 18, 2023) 21  Recommended Resources (Part 1) LOOKUP function. support.microsoft.com. https://support.microsoft.com/en-us/office/lookup-function-446d94af-663b-451d-82 51-369d5e3864cb (Accessed Oct 18, 2023) VLOOKUP function. support.microsoft.com. https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8 ab8-93a18ad188a1 (Accessed Oct 18, 2023) XLOOKUP function. support.microsoft.com. https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-8 4f9-88eae8bf5929 (Accessed Oct 18, 2023) Focus on the XLOOKUP function which is the newest. 22  Recommended Resources (Part 2) Reference Textbook: Michael Alexander and Dick Kusleika. 2022. Microsoft Excel 365 Bible. 1st Ed. John Wiley & Sons, Inc., Hoboken, New Jersey. Print ISBN: 978-1-119-83510-3. Ch 15, 22 (pp 539). 23