What Are HLookup & VLookup in Microsoft Excel 2007?
HLookup vs. VLookup
- The HLookup function will search across specified horizontal rows of data in your Excel worksheet. The VLookup searches through vertical columns of data. Similar to the way a database program acts, you are able to look up an entry and then use the matching data to work with a formula or simply have the matched entry populate a specific cell.
- You must use a master data list and a specified key value to produce the desired results when performing your look-up. The number of columns and rows of data you can include for use with VLookup or HLookup are not limited.
Creating the Master Data List
- In the first cell in your data range, type a title, for example, "salesperson." A data range is a collection or group of selected cells. An example of a data range reference is A1:K50, indicating the values for the data list are located in cells A1 and all cells up to and including K50. Next, enter the names of the salespeople in the remaining cells in that column, and add other columns of data that you may be referencing in your look-ups.
Specifying the Key Value
- The VLookup requires the data you will use as your key value to be located in the first cell on the left. Specify the key value, sometimes called the look-up value, by putting the proper value in the proper location on your worksheet. On a new worksheet, for example, you could put the key value, "Salesperson" in cell A1. This specifies that you will look up information by salesperson. The HLookup generally references a row header and then uses a value listed.
- An example of how to use the VLookup or a HLookup function as part of a formula is to look up and use data from one worksheet and another data range, or even an entirely separate workbook. An example of such a use could be looking up a salesperson's commission on one worksheet, which lists the salesperson and the commission rate he earns, and then adding that commission to another worksheet, which contains his base salary.
Keep in mind, matches can be determined by literal and exact matches or by approximate value matches as well. Sorting the master data list in ascending order, you can often obtain a closest match without exceeding the requested look-up value.