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

Finding approximate matches pertains only when you’re looking up numeric entries (rather than text) in the first column or row of the vertical or hori- zontal Lookup table. When Excel doesn’t find an exact match in this Lookup column or row, it locates the next highest value that doesn’t exceed the lookup_value argument and then returns the value in the column or row designated by the col_index_num or row_index_num arguments.
When using the VLOOKUP and HLOOKUP functions, the text or numeric entries in the Lookup column or row (that is, the leftmost column of a ver- tical Lookup table or the top row of a horizontal Lookup table) must be unique. These entries must also be arranged or sorted in ascending order; that is, alphabetical order for text entries, and lowest-to-highest order for numeric entries. (See Book VI, Chapter 1 for detailed information on sorting data in a spreadsheet.)
Figure 6-1 shows an example of using the VLOOKUP function to return either a 15% or 20% tip from a tip table, depending on the pretax total of the check. Cell F3 contains the VLOOKUP function:
=VLOOKUP(Pretax_Total,Tip_Table,IF(Tip_Percentage=0.15,2,3))
This formula returns the amount of the tip based on the tip percentage in cell F1 and the pretax amount of the check in cell F2.
Lookup and Reference 411
Figure 6-1:
Using the VLOOKUP function to return the amount of the tip to add from a Lookup table.
Book III Chapter 6
Lookup, Information, and Text Formulas