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 C2C7. 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 C2E7 (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 threerow 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 