Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Problem In the given spreadsheet, columns A though E represent a data table. However, there are missing values in columns C through E . In
Problem
In the given spreadsheet, columns A though represent a data table. However, there are missing values in columns through In cell there is a lookup table containing columns for Product, Product ID and Unit Price. Your first task is to fill in the missing values in columns C D and E using VLOOKUP and the lookup table. Doing this manually would take a long time and be very tediousi The best way is to use VLOOKUP to fill in the first row of missing data and then drag down to fill the remaining rows.
In cell C populate the Product ID of the first item using VLOOKUP. The value you wish to look up in the lookup table is in Column A cell A to be exact. The lookup table is cell range $$:$$tilde Note that these are absolute cell references since the look up table is in a fixed location. The Product ID is in the second column of the lookup table. And finally set the rangelookup parameter to FALSE.
What is the Product ID returned in cell C
Now repeat this process for the missing Unit Price in cell D What is the unit price returned in Dto the nearest cent $
And finally, for the first item, calculate the Gross Sales in column E using Order Quantity from column B and Unit Price from Column D What is the gross sale amount in cell E
To complete the data table, select cells C D and E together then drag down or doubleclick the fill sign in the lower right of those three selected cells. And there you have it The remaining items in columns C D and E have been completed using a lookup table, two VLOOKUP formulas, a simple math formula, and Excel's fill down feature.
Combining VLOOKUP with a Pivot Table
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started