﻿ Excel 2007 exercises: Lookup Vlookup Hlookup

# Excel 2007 exercises: Lookup Vlookup Hlookup

## Lookup Vlookup Hlookup

In this exercise, you will learn to use Lookup, Hlookup, and Vlookup functions to look for values in a data sheet.

 A B C D E F 1 OrderID CustomerID EmployeeID OrderDate RequiredDate ShippedDate 2 10330 LILAS 3 11/16/1994 12/14/1994 11/28/1994 3 10331 BONAP 9 11/16/1994 12/28/1994 11/21/1994 4 10332 MEREP 3 11/17/1994 12/29/1994 11/21/1994 5 10333 WARTH 5 11/18/1994 12/16/1994 11/25/1994 6 10334 VICTE 8 11/21/1994 12/19/1994 11/28/1994 7 10335 HUNGO 7 11/22/1994 12/20/1994 11/24/1994 8 10336 PRINI 7 11/23/1994 12/21/1994 11/25/1994 9 10337 FRANK 4 11/24/1994 12/22/1994 11/29/1994 10 10338 OLDWO 4 11/25/1994 12/23/1994 11/29/1994 11 10339 MEREP 2 11/28/1994 12/26/1994 12/5/1994 12 10340 BONAP 1 11/29/1994 12/27/1994 12/9/1994 13 10341 SIMOB 7 11/29/1994 12/27/1994 12/6/1994
 1.Display the name of customer who purchased products with OrderID=10332 In cell A16 write the formula: =LOOKUP(10332,A2:A15,B2:B15) Note: The data in A2:A99 needs to be sorted in ascending order 2.Display the name of employee who processed this order In cell A17 write the formula: =LOOKUP(10332,A2:A15,C2:C15) 3.Display the date of this order In cell A18 write the formula: =LOOKUP(10338,A2:A15,D2:D15) 4.Display the OrderID in 10th row of the table In cell A19 write the formula: =HLOOKUP("OrderID",A1:F15,10,FALSE) 5.Display the customer name and required dateof  this order In cell A20 write the formula: =VLOOKUP(A19,A2:F15,2,FALSE) In cell A21 write the formula: =VLOOKUP(A19,A2:F15,5,FALSE) Syntax: LOOKUP(lookup_value,lookup_vector,result_vector) VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)