Expert answer:Using VLOOKUP in Excel

Answer & Explanation:question requirements in the A7 Vlookup.pdfA7 Vlookup data.xlsx A7 Vlookup.pdf
a7_vlookup_data.xlsx

a7_vlookup.pdf

Unformatted Attachment Preview

Product Code
Product Name
A7785529
Solid brass umbrella stand
A7319212
Tailored valance
A1120964
Ballerina gown
A0274004
Beaded thong
A7812235
A7447139
A7321077
A3438423
A3808151
A7944519
A7222214
A7256600
A0602434
A8269116
A7702051
A7710147
A8461133
A3438412
A7288043
A7223920
A3438307
A7709540
A5657601
A5452127
A7727396
A6099449
Wood butcher block
Shoe armoire
Pinch-pleated draperies
Ottoman
Keepsake satin dress
Ball and claw curio
Embroidered bed-in-a-bag
Quebec featherbed
Verdi from Rockport
Cable sweater
Beaded floor lamp
Brass desk clock
Eyelet tunic
Slip-covered glider
Accent pillow
Pillow sham
Convertible crib
Light chandelier
Ferrar dress pants
Painter’s overalls
Accent mirror
New Balance running shoe
Unit CostCountry of Origin
$99.00 India
$15.00 USA
$19.99 USA
$14.00 China
$299.99
$119.00
$28.00
$129.99
$59.99
$399.99
$179.99
$89.99
$97.99
$29.00
$99.00
$19.00
$49.00
$399.99
$15.99
$35.99
$199.99
$599.99
$32.00
$29.99
$69.00
$75.95
Thailand
China
USA
Canada
USA
USA
USA
USA
China
USA
China
India
USA
Canada
USA
USA
Taiwan
China
USA
USA
India
China
FirstName
Pascal
Christophe
Loïc
Christophe
Florian
Eric
Benjamin
Emmanuel
Arnaud
Vincent
Kenneth
Richard
Clément
Olivier
Student Table
LastNames Total Marks Grade
Seguin
59
Stenac
48
Teulière
87
Pracht
70
Puig
36
Self
54
Shepherd
25
Schauly
56
Levesque
66
Massiot
75
Petit
32
Mutricy
42
G.Pflug
37
Minier
97
Scores
0
50
65
75
85
95
Grades
F
E
D
C
B
A
FirstName
Pascal
Christophe
Loïc
Christophe
Florian
Eric
Benjamin
Emmanuel
Arnaud
Vincent
Kenneth
Richard
Clément
Olivier
Student Table
LastNames Total Marks Grade
Seguin
59
Stenac
48
Teulière
87
Pracht
70
Puig
36
Self
54
Shepherd
25
Schauly
56
Levesque
66
Massiot
75
Petit
32
Mutricy
42
G.Pflug
37
Minier
97
Scores
0
50
65
75
85
95
Grades
F
E
D
C
B
A
Excel Lookup and Reference Functions: VLOOKUP
Background
According to Microsoft Excel VLOOKUP can be defined as a function, “that looks for a
value in the leftmost column of a table, and then returns a value in the same row from a
column you specify. By default the table must be sorted in an ascending order.”
This function is mostly used for following tasks:
1.
To look up a single or a set of values from a data sheet.
2.
To add a column to a datasheet from some other table, based on some unique
(attribute).
Its syntax is as follows:
=VLOOKUP( lookup_value, table_array, column_index, range_lookup )
Here, ‘lookup_value’ specifies the value to be searched inside the ‘table_array’. It can
either be a value or a reference.
‘table_array’ is the range with two or more columns. ‘table_array’ argument can receive a
range reference or a named range. The leftmost column of this range must contain the
‘lookup_value’.
‘column_index’ is the relative index of the column whose value needs to be returned by
the VLOOKUP function. A ‘column_index’ 1 would return values from the first column
in the ‘table_array’ similarly ‘column_index’ 2 would return values from the second
column in the ‘table_array’.
‘range_lookup’ is a Boolean value that specifies whether you want VLOOKUP to find an
exact match or an approximate match. If its value is ‘True’ then either an approximate or
an exact match will be returned. Here, if an exact match is not found, the next value that
is less than ‘lookup_value’ is returned. If its value is ‘False’ then only exact match will
be returned.
Part 1 (50 points)
A list can be used as a lookup table. In the lookup table, you can use a worksheet
formula to find one item of data that is associated with another item of data in the same
list. Suppose you have a list of every item sold by Lowe’s Home Improvement or Office
Depot or J C Penney Company. Just think of the thousands of items listed to be sold.
Each column in the list described characteristics, such as product code, product name,
unit cost, country of origin, etc.
Suppose this list contains the following: a Product Code in Column A, a Product Name
in Column B, the Unit Price in Column C, and Country of Origin in Column D. Most
lists will have many columns describing the items. If you know the Product Code, then
you can find the Product Name, Unit Price, and Made Where.
In this part of the assignment:
lookup_value argument
Specify the search value you want to compare with the
value in your list (Product Code). The search value maybe
the relative or absolute reference to the cell where the
search value is located, such as G4 or $G$4.
table_array argument
Specify the range containing the entire list, such as that
your list resides i.e. A1:D368.
col_index_num argument
Identifies the column containing the value you want
returned, such as Product Name or Column 2.
range_lookup argument
Tells Excel how to compare the search value with the first
column. You may enter True or False. This range lookup
is a logical value (True or False) that specifies whether you
want VLOOKUP to find an exact match (FALSE) or an
approximate match (TRUE). The following points
illustrate the use of this argument in this assignment..
a.
Enter False if you want Excel to find an exact match for the search value.
If one is not found, the value #N/A is returned.
b.
Enter True if you want Excel to find an approximate match for the search
value. If an exact match is not found, it uses the next largest value that is
less than or equal to lookup value.
c.
In most cases, you would want to enter False. If you enter False, the table
does not need to be sorted.
d.
If you enter True, the values in the first column of the table must be in
ascending order. Otherwise, a correct value may not be given.
e.
Search value is the item of data (in this case the Product Code i.e.
A7785529) you use to find information that is related to the Product Code
i.e Product Name, Unit Cost, and Country of Origin.
Follow the steps listed below to use the VLOOKUP function to locate information given
the Product Code
1.
Create a new workbook and copy the data below into a worksheet (Select the
entire table and paste to worksheet).
Product
Code
A7785529
A7319212
A1120964
A0274004
A7812235
A7447139
A7321077
A3438423
A3808151
A7944519
A7222214
A7256600
A0602434
A8269116
A7702051
A7710147
A8461133
A3438412
A7288043
A7223920
A3438307
A7709540
A5657601
A5452127
A7727396
A6099449
Product Name
Solid brass umbrella stand
Tailored valance
Ballerina gown
Beaded thong
Wood butcher block
Shoe armoire
Pinch-pleated draperies
Ottoman
Keepsake satin dress
Ball and claw curio
Embroidered bed-in-a-bag
Quebec featherbed
Verdi from Rockport
Cable sweater
Beaded floor lamp
Brass desk clock
Eyelet tunic
Slip-covered glider
Accent pillow
Pillow sham
Convertible crib
Light chandelier
Ferrari dress pants
Painter’s overalls
Accent mirror
New Balance running shoe
Unit Cost
$99.00
$15.00
$19.99
$14.00
$299.99
$119.00
$28.00
$129.99
$59.99
$399.99
$179.99
$89.99
$97.99
$29.00
$99.00
$19.00
$49.00
$399.99
$15.99
$35.99
$199.99
$599.99
$32.00
$29.99
$69.00
$75.95
Country of
Origin
India
USA
USA
China
Thailand
China
USA
Canada
USA
USA
USA
USA
China
USA
China
India
USA
Canada
USA
USA
Taiwan
China
USA
USA
India
China
2.
Select any cell after the last column of the list, such as F5, to enter the search
value, such as A5452127, which is the Product Code.
3.
Select cell F6 and type a formula that uses the VLOOKUP syntax; use False.
Remember: The VLOOKUP function compares the search value with the first
column in the list and then returns an associated value in the same row. To find
the Product Name, you would enter 2 as the col_index_num.
4.
Select cell F7 and type a formula that uses the VLOOKUP syntax; use False. To
find Unit Price, enter 3 as the col_index_num.
5.
Select cell F8 and type a formula that uses the VLOOKUP syntax; use False. To
find Made Where, enter 4 as the col_index_num.
If your three formulas are correct, you should display for Product Code A5452127 the
following: Painter’s overalls for the Product Name in F6, $29.99 for the Unit Cost in F7,
and USA for the Country of Origin in F8.
Verify that your function is working by entering other Product Codes in cell F5 and
seeing the values produced in cells F6:F8.
Your worksheet should look similar to the following.
Part 2 (25 points)
Translating scores to grades using vertical lookup. Let’s say we have a table with student
scores and now we have to assign them a grade based on their scores.
Enter the following data into a new worksheet named “Grades”
Type the VLOOKUP function as as: =VLOOKUP(C3,$F$4:$G$9,2,TRUE) and then copy
this formula to the cells in the Grade column. .
Explanation:
The first argument to the function i.e. ‘lookup_value’ = C3 (Reference for first

element)
Second argument i.e. ‘table_array’ = $F$4:$G$9 (Range of scores and grades

table)
Third argument i.e. ‘column_index’ = 2 (the column number whose value the

VLOOKUP function should return)
Fourth argument i.e. ‘range_lookup’ = True (It matches both the exact values

and values slightly lesser)
Part 3
Applying Vertical lookup using a table on another sheet or another workbook. In all the
above you have applied VLOOKUP on tables that are on same worksheet. Now let’s redo
the task in previous assignment but this time with the scores and grade table present on
another worksheet sheet
In this example the “Student Table” is on ‘Sheet1’ while the “Grade and Score table” is
on the ‘Sheet2’. So you can use a VLOOKUP
as: =VLOOKUP(C3,Sheet2!$B$3:$C$8,2,TRUE)
Explanation:

The first argument to the function i.e. ‘lookup_value’ = C3 (Reference for first
element)

Second argument i.e. ‘table_array’ = Sheet2!$B$3:$C$8 (Range of scores and
grades table which is present on Sheet2)

Third argument i.e. ‘column_index’ = 2 (the column number whose value the
vertical lookup function should return)

Fourth argument i.e. ‘range_lookup’ = True (It matches both exact and values
slightly lesser)
Tip: While writing such VLOOKUP formulas you can simply navigate to the Sheet
where the table is present and then select the required table. This will automatically insert
the reference of that particular table in the formula.
Please follow our naming convention for all work to be submitted (Don’t forget, if you do
not follow the naming convention, it will not be graded)
Name the first worksheet “Part 1” and the second worksheet “Part 2” and the third
worksheet “Lookup Table” the lookup table for Part 2.
Deliverables: The workbook above.

Purchase answer to see full
attachment

How it works

  1. Paste your instructions in the instructions box. You can also attach an instructions file
  2. Select the writer category, deadline, education level and review the instructions 
  3. Make a payment for the order to be assignment to a writer
  4.  Download the paper after the writer uploads it 

Will the writer plagiarize my essay?

You will get a plagiarism-free paper and you can get an originality report upon request.

Is this service safe?

All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your essay today and save 20% with the discount code ESSAYHELP