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

seo comment

 seo

Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword....wait there's even more Now what if i told you there was a simple Wordpress plugin that does all the On-Page SEO, and automatically for you? That's right AUTOMATICALLY, just watch this 4minute video for more information at. <a href="http://www.SEORankingLinks.us">Seo Plugin</a>
seo http://www.SEORankingLinks.us/


2016-09-25
CAPTCHA image



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.