By The most popular of the lookup functions in Excel 2007 are HLOOKUP (for Horizontal Lookup) and VLOOKUP (for Vertical Lookup). These functions are located in the Lookup & Reference category on the Formulas tab of the Ribbon and in the Insert Function dialog box. They are part of a powerful group of functions that can return values by looking them up in data tables. The HLOOKUP function searches horizontally (left to right) the topmost row of a table until it locates a value that matches or exceeds the one that you’re looking up. The VLOOKUP function searches vertically (top to bottom) the leftmost column of a table until the program locates a value that matches or exceeds the one you are looking up. 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 vertical Lookup table or the top row of a horizontal Lookup table) must be unique.
Tagged as: lookups, tutorials, vlookup To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the most popular Excel books on Amazon. VLOOKUP MATCH is one of several possible lookup formulas within Microsoft Excel. This tutorial assumes you already have a decent understanding of how to use VLOOKUP. Explanation and Examples of the Excel HLOOKUP Function - Used to Perform a Horizontal Lookup in Excel.
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. The HLOOKUP function uses the following syntax: =HLOOKUP(lookupvalue,tablearray,rowindexnum,rangelookup) The VLOOKUP function follows the nearly identical syntax: =VLOOKUP(lookupvalue,tablearray,colindexnum,rangelookup) In both functions, the lookupvalue argument is the value that you want to look up in the table, tablearray is the cell range or name of the table that contains both the value to look up and the related value to return. The rowindexnum argument in the HLOOKUP function is the number of the row whose value you want to return; the colindexnum argument in the VLOOKUP function is the number of the column whose value you want to return.
The optional rangelookup argument in both the VLOOKUP and the HLOOKUP functions is the logical TRUE or FALSE that specifies whether you want Excel to find an exact or approximate match for the lookupvalue in the tablearray. When you specify TRUE or omit the rangelookup argument, Excel finds an approximate match. When you specify FALSE as the rangelookup argument, Excel finds only exact matches. Finding approximate matches pertains only when you’re looking up numeric entries (rather than text).
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 lookupvalue argument and then returns the value in the column or row designated by the colindexnum or rowindexnum arguments. The figure below 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(PretaxTotal,TipTable,IF(TipPercentage=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. Using the VLOOKUP function to return the amount of the tip to add from a Lookup table. To use this tip table, enter the percentage of the tip (15% or 20%) in cell F1 (named TipPercentage) and the amount of the check before tax in cell F2 (named PretaxTotal). Excel then looks up the value that you enter in the PretaxTotal cell in the first column of the Lookup table, which includes the cell range A2:C101 and is named TipTable. Excel then moves down the values in the first column of TipTable until it finds a match.
If Excel finds that the value entered in the PretaxTotal cell ($16.50 in this example) doesn’t exactly match one of the values in the first column of TipTable, 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 colindexnum argument of the VLOOKUP function (this is because the optional rangelookup argument has been omitted from the function). Notice that the colindexnumber argument uses an IF statement to determine which column’s value to return. In this case, if the TipPercentage value is 0.15, then the function returns the value in the second column of the table in the row that was identified by the lookupvalue.
Otherwise, it returns the value in the third column of the same row.