Page 430 - Excel 2013 All-in-One For Dummies

412 Lookup and Reference
To use this tip table, enter the percentage of the tip (15% or 20%) in cell F1 (named Tip_Percentage) and the amount of the check before tax in cell F2 (named Pretax_Total). Excel then looks up the value that you enter in the Pretax_Total cell in the first column of the Lookup table, which includes the cell range A2:C101 and is named Tip_Table.
Excel then moves down the values in the first column of Tip_Table until it finds a match, whereupon the program uses the col_index_num argument in the VLOOKUP function to determine which tip amount from that row of the table to return to cell F3. If Excel finds that the value entered in the Pretax_ Total cell ($16.50 in this example) doesn’t exactly match one of the values in the first column of Tip_Table, the program continues to search down the comparison range until it encounters the first value that exceeds the pretax total (17.00 in cell A19 in this example). Excel then moves back up to the previous row in the table and returns the value in the column that matches the col_index_num argument of the VLOOKUP function. (This is because the optional range_lookup argument has been omitted from the function.)
Note that the tip table example in Figure 6-1 uses an IF function to determine the col_index_num argument for the VLOOKUP function in cell F3. The IF func- tion determines the column number to be used in the tip table by matching the percentage entered in Tip_Percentage (cell F1) with 0.15. If they match, the function returns 2 as the col_index_num argument and the VLOOKUP function returns a value from the second column (the 15% column B) in the Tip_Table range. Otherwise, the IF function returns 3 as the col_index_num argument and the VLOOKUP function returns a value from the third column (the 20% column C) in the Tip_Table range.
Figure 6-2 shows an example that uses the HLOOKUP function to look up the price of each bakery item stored in a separate price Lookup table and then to return that price to the Price/Doz column of the Daily Sales list. Cell F3 contains the original formula with the HLOOKUP function that is then copied down column F:
=HLOOKUP(item,Price_table,2,FALSE)
In this HLOOKUP function, the range name Item that’s given to the Item column in the range C3:C62 is defined as the lookup_value argument and the cell range name Price table that’s given to the cell range I1:M2 is the table_ array argument. The row_index_num argument is 2 because you want Excel to return the prices in the second row of the Prices Lookup table, and the optional range_lookup argument is FALSE because the item name in the Daily Sales list must match exactly the item name in the Prices Lookup table.