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)





Comments

Name:
Comment:
Enter the text:
CAPTCHA image

...................................................................................................................................Home | Forum | About | Contact
This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word.
However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.
Copyright @ 2011-2013 worldbestlearningcenter. All Rights Reserved.
Home Programming Web DB & App Questions Exercises Tips Programs FAQs Download About
Computer-Wbest
Popular Pages
You might like