Look up values with VLOOKUP, INDEX, or MATCH
You have a list of office numbers, and you need to know which employees are in each office. But the spreadsheet is huge, so what can you do? Use a lookup function. The VLOOKUP and HLOOKUP functions are two of the most useful, and so are INDEX and MATCH.
Note: If you're trying to find the Lookup Wizard, that feature is no longer part of Excel.
Here's a quick reminder of how to use VLOOKUP.
=VLOOKUP(B2,C2:E7,3,TRUE)
The first argument—piece of data the function needs to work—is the value you want to find. That can be a cell reference or a hard value such as "smith" or 21,000. The second argument is the range of cells that you think contains the value you want to find. In this example, it's C2-C7. The third argument is the column in that range of cells that contains the value you want to see.
The fourth argument is optional. You can enter True or False. If you enter TRUE, or leave the argument blank, the function returns an approximate match of the value you specify in the first argument. If you enter FALSE, the function will match the value provide by the first argument. In other words, leaving the fourth argument blank, or entering TRUE, gives you more flexibility.
This example shows you how the function works. When you enter a value in cell B2 (the first argument), VLOOKUP searches cells C2-E7 (the second argument) and returns the closest approximate match from the third column in the range, column E (the third argument).
The fourth argument is blank, so the function returns an approximate match. If it didn't, you'd have to enter one of the values in columns C or D to get a result at all.
Once you're comfortable with VLOOKUP, the HLOOKUP function shouldn't be too hard to use. You enter the same arguments, but it finds values in rows instead of columns.
Give it a try
If you want to play around with lookup functions before you try them out with your own data, here's some sample data. Some people like using VLOOKUP and HLOOKUP, others prefer using INDEX and MATCH together. Try each method and see which ones you like best.
VLOOKUP at work
Copy all the cells in this table and paste it into cell A1 on a blank worksheet in Excel.
Hint Before you paste the data into Excel, set the column widths for columns A through C to 250 pixels, and click Wrap Text (Home tab, Alignment group).
| Density | Viscosity | Temperature |
| 0.457 | 3.55 | 500 |
| 0.525 | 3.25 | 400 |
| 0.606 | 2.93 | 300 |
| 0.675 | 2.75 | 250 |
| 0.746 | 2.57 | 200 |
| 0.835 | 2.38 | 150 |
| 0.946 | 2.17 | 100 |
| 1.09 | 1.95 | 50 |
| 1.29 | 1.71 | 0 |
| Formula | Description | Result |
| '=VLOOKUP(1,A2:C10,2) | Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A which is 0.946, and then returns the value from column B in the same row. | =VLOOKUP(1,A2:C10,2) |
| '=VLOOKUP(1,A2:C10,3,TRUE) | Using an approximate match, searches for the value 1 in column A, finds the largest value less than or equal to 1 in column A, which is 0.946, and then returns the value from column C in the same row. | =VLOOKUP(1,A2:C10,3,TRUE) |
| '=VLOOKUP(0.7,A2:C10,3,FALSE) | Using an exact match, searches for the value 0.7 in column A. Because there is no exact match in column A, an error is returned. | =VLOOKUP(0.7,A2:C10,3,FALSE) |
| '=VLOOKUP(0.1,A2:C10,2,TRUE) | Using an approximate match, searches for the value 0.1 in column A. Because 0.1 is less than the smallest value in column A, an error is returned. | =VLOOKUP(0.1,A2:C10,2,TRUE) |
| '=VLOOKUP(2,A2:C10,2,TRUE) | Using an approximate match, searches for the value 2 in column A, finds the largest value less than or equal to 2 in column A, which is 1.29, and then returns the value from column B in the same row. | =VLOOKUP(2,A2:C10,2,TRUE) |
HLOOKUP at work
Copy all the cells in this table and paste it into cell A1 on a blank worksheet in Excel.
Hint Before you paste the data into Excel, set the column widths for columns A through C to 250 pixels, and click Wrap Text (Home tab, Alignment group).
| Axles | Bearings | Bolts |
| 4 | 4 | 9 |
| 5 | 7 | 10 |
| 6 | 8 | 11 |
| Formula | Description | Result |
| '=HLOOKUP("Axles", A1:C4, 2, TRUE) | Looks up "Axles" in row 1, and returns the value from row 2 that's in the same column (column A). | =HLOOKUP("Axles",A1:C4,2,TRUE) |
| '=HLOOKUP("Bearings", A1:C4, 3, FALSE) | Looks up "Bearings" in row 1, and returns the value from row 3 that's in the same column (column B). | =HLOOKUP("Bearings",A1:C4,3,FALSE) |
| '=HLOOKUP("B", A1:C4, 3, TRUE) | Looks up "B" in row 1, and returns the value from row 3 that's in the same column. Because an exact match for "B" is not found, the largest value in row 1 that is less than "B" is used: "Axles," in column A. | =HLOOKUP("B",A1:C4,3,TRUE) |
| '=HLOOKUP("Bolts", A1:C4, 4) | Looks up "Bolts" in row 1, and returns the value from row 4 that's in the same column (column C). | =HLOOKUP("Bolts",A1:C4,4) |
| '=HLOOKUP(3, {1,2,3;"a","b","c";"d","e","f"}, 2, TRUE) | Looks up the number 3 in the three-row array constant, and returns the value from row 2 in the same (in this case, third) column. There are three rows of values in the array constant, each row separated by a semicolon (;). Because "c" is found in row 2 and in the same column as 3, "c" is returned. | =HLOOKUP(3,{1,2,3;"a","b","c";"d","e","f"},2,TRUE) |
INDEX and MATCH in action
This example uses the INDEX and MATCH functions together to return the earliest invoice number and its corresponding date for each of five cities. Because the date is returned as a number, we use the TEXT function to format it as a date. The INDEX function actually uses the result of the MATCH function as its argument. The combination of the INDEX and MATCH functions are used twice in each formula – first, to return the invoice number, and then to return the date.
Copy all the cells in this table and paste it into cell A1 on a blank worksheet in Excel.
Hint Before you paste the data into Excel, set the column widths for columns A through D to 250 pixels, and click Wrap Text (Home tab, Alignment group).
| Invoice | City | Invoice Date | Earliest invoice by city, with date |
| 3115 | Atlanta | 4/7/12 | ="Atlanta = "&INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Atlanta",$B$2:$B$33,0),3),"m/d/yy") |
| 3137 | Atlanta | 4/9/12 | ="Austin = "&INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Austin",$B$2:$B$33,0),3),"m/d/yy") |
| 3154 | Atlanta | 4/11/12 | ="Dallas = "&INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Dallas",$B$2:$B$33,0),3),"m/d/yy") |
| 3191 | Atlanta | 4/21/12 | ="New Orleans = "&INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("New Orleans",$B$2:$B$33,0),3),"m/d/yy") |
| 3293 | Atlanta | 4/25/12 | ="Tampa = "&INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),1)& ", Invoice date: " & TEXT(INDEX($A$2:$C$33,MATCH("Tampa",$B$2:$B$33,0),3),"m/d/yy") |
| 3331 | Atlanta | 4/27/12 | |
| 3350 | Atlanta | 4/28/12 | |
| 3390 | Atlanta | 5/1/12 | |
| 3441 | Atlanta | 5/2/12 | |
| 3517 | Atlanta | 5/8/12 | |
| 3124 | Austin | 4/9/12 | |
| 3155 | Austin | 4/11/12 | |
| 3177 | Austin | 4/19/12 | |
| 3357 | Austin | 4/28/12 | |
| 3492 | Austin | 5/6/12 | |
| 3316 | Dallas | 4/25/12 | |
| 3346 | Dallas | 4/28/12 | |
| 3372 | Dallas | 5/1/12 | |
| 3414 | Dallas | 5/1/12 | |
| 3451 | Dallas | 5/2/12 | |
| 3467 | Dallas | 5/2/12 | |
| 3474 | Dallas | 5/4/12 | |
| 3490 | Dallas | 5/5/12 | |
| 3503 | Dallas | 5/8/12 | |
| 3151 | New Orleans | 4/9/12 | |
| 3438 | New Orleans | 5/2/12 | |
| 3471 | New Orleans | 5/4/12 | |
| 3160 | Tampa | 4/18/12 | |
| 3328 | Tampa | 4/26/12 | |
| 3368 | Tampa | 4/29/12 | |
| 3420 | Tampa | 5/1/12 | |
| 3501 | Tampa | 5/6/12 |
No comments:
Post a Comment