Forum Discussion
EXCEL Homework help
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.
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.
-
Change the date column format to "short date":
- Select the date column.
- Right-click > Format Cells > Date > Choose format > OK.
-
Check VLOOKUP errors:
- Ensure lookup values match format.
- Confirm correct lookup range and column index.
- If using approximate match, ensure range is sorted.
By following these steps, you should be able to correct the date format and resolve the #N/A errors in your VLOOKUP formulas. If anyone continues to have trouble, feel free to hire an Excel expert to further investigate the details or formulas.
-
