The Lookup, Index, and Match functions include some advanced techniques that will be explored in this chapter. We'll begin with a spreadsheet containing data of ingredients and consumption history for eight weeks from a student-run business. The data includes prices, vendors, and locations. We need to fill in the item class information determined by price ranges.
Use this workbook for the chapter.
We will create a table including Min (minimum), Max (maximum), and Class columns. Each row will determine a separate price range beginning with $0.00 minimum to $5.00 maximum and increasing by $5.00 thereafter. The final range is $25.00 to $30.00 which will be designated class A with lower ranges descending through B, C, D, E, and F. The table should be renamed for quick reference; for this example, it is named classTable. (See Figure 27.2)
We'll return to the item class cells in the data spreadsheet and use the VLOOKUP function to look up the Price Per Unit within the designated classTable array to identify the class letter in column 3. Lastly, the function needs to return a TRUE value for an approximate match.
Note: A TRUE argument determines a value based on approximation within a range, whereas a FALSE argument can only return a value if the target reference matches the minimum or maximum range numbers. In this exercise, the target references vary between the specific range points, so the function needs to find an approximate value.
The Row and Column functions ask for an optional reference argument. The functions work by identifying the referenced cell's numerical value in the spreadsheet. For example, =ROW(A1) will return a value of 1 because the referenced cell, A1, is in row 1 of the spreadsheet. The function =ROW(E1) will also return a value of 1 because the referenced cell is still in row 1. If the reference cell is in a different row of the spreadsheet, the function will return the relevant value of the row. The Column function works similarly to identify the numerical position of the column regardless of the referenced cell's row position.
If no cell is referenced in the Row or Column function, the value of the function's current cell position will be returned. For example, if =COLUMN() is inserted in cell E10, it will return the value 5 because the function's cell is in column E which is the fifth column in the spreadsheet.
On their own, the Row and Column functions are not particularly useful. However, when they are used in conjunction with another function, they can simplify that function.
In this exercise, we want to create afor Figure 27.1's data to look up information about a particular ingredient. We can copy and paste the data spreadsheet's column labels to a new spreadsheet and insert a data validation list of the ingredients under the appropriate Ingredient column. Next, we need to write a VLOOKUP function beginning with the Vendor column.
Note: Formatting may need to be reapplied for the data to match with the data spreadsheet.
The completed dashboard should now reflect the data on the data spreadsheet for the selected ingredient.
The Index and Match functions can accomplish the same result. In this case, the Match function working within Index is used to search for and match a row in the data table with a designated value. Then it draws a value from a specified column number which will be identified using the Column function again.
The final function that we can use to lookup information about a particular ingredient from the data spreadsheet is the XLOOKUP function. XLOOKUP works by looking up a designated value from a selected array and returning the associated value of another related array. Additionally, the XLOOKUP function can return a predetermined value or textual note if the initial lookup value cannot be found.
This content is provided to you freely by BYU-I Books.
Access it online or download it at https://books.byui.edu/bus_115_business_app/advanced_lookup_func.