Forum Discussion
EXCEL Homework help
I have this homework for one of my classes and we have to use the VLOOKUP formula for these
Here were the instructions given to us:
Task 1: Complete lookup value from columns I:L of Transactions Sheet
Task 2: Using PIVOT table, create a summary of total sales per year which can be filtered according to product category (answer on Summary 1)
Task 3: Using PIVOT table, create a summary of total profit and total quantity sold to each customer segment which can be filtered according to product category (answer on Summary 2)
Task 4: Using PIVOT table, create a summary of total discount, Quantity, Sale and Profit sold according to Product Sub-Category which can be filtered by Customer segment.
I managed to figure out the LOOKUP for the others but the order date seems to only display numbers and not dates. I repeated the formula over and over but I can't seem to get it... Am I doing something wrong? Would also appreciate some extra help on why the values are displaying as #N/A especially towards the bottom of the sheet
I asked my professor for help and his response did not help me with this in any way... What he told me was "All you need to do is change the data type of that column to short date"
Your formulas still have relative references to the lookup ranges, so they will change when you fill or copy down the formulas.
The formula =VLOOKUP(D2,Product!A159:D2052,2,FALSE) in J2 will change to =VLOOKUP(D3,Product!A160:D2053,2,FALSE) in D3, ..., and to =VLOOKUP(D200,Product!A357:D2250,2,FALSE) in J200.
So if D200 is in Product!A100, the formula won't find it since it starts looking from A357 down. Hence the #N/A errors.
Please read my previous reply again. I explained how to change the reference to the lookup range to absolute.
6 Replies
Select column I.
On the Home tab of the ribbon, in the Number group, select Short Date from the Number Format drop-down.
In your formula =VLOOKUP(B2, Orders!A2:D5010, 2, FALSE), the references to A2 and to A2:D5010 are relative, i.e. they will be adjusted automatically when you fill or copy down from I2.
So in I3, the formula will become =VLOOKUP(B3, Orders!A3:D5011, 2, FALSE). That is what you want for the lookup value B2 changing to B3. But the lookup range should not change, you want it to remain Orders!A2:D5010. You can do this by making the reference to that range absolute,
Select I2.
Select A2:D5010 in the formula.
Press F4.
The reference will change to $A$2:$D$5010.
Now fill down. The formula in I3 will now be =VLOOKUP(B3, Orders!$A$2:$D$5010, 2, FALSE)
B2 has changed to B3, but $A$2:$D$5010 has remained the same.
HansVogelaar Already did that... now the problem are the cells with #N/A
Column J: =VLOOKUP(D2,Product!A159:D2052,2,FALSE)
Column K: =VLOOKUP(D2,Product!A2:D1895,3,FALSE)
Column L: =VLOOKUP(C2,Customer!A2:D794,3,FALSE)
I can't seem to get rid of the #N/A but when i check the reference sheets the info is there it just won't appear in the cell
Your formulas still have relative references to the lookup ranges, so they will change when you fill or copy down the formulas.
The formula =VLOOKUP(D2,Product!A159:D2052,2,FALSE) in J2 will change to =VLOOKUP(D3,Product!A160:D2053,2,FALSE) in D3, ..., and to =VLOOKUP(D200,Product!A357:D2250,2,FALSE) in J200.
So if D200 is in Product!A100, the formula won't find it since it starts looking from A357 down. Hence the #N/A errors.
Please read my previous reply again. I explained how to change the reference to the lookup range to absolute.
