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.
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.
-
- I FIXED IT!!! Thank you so much!!!
LouiseL12 Hey there.
For most cases while debugging an Excel error, it is the smartest method to define possible cases, and use an IFERROR excel formula help to define ELSE action plan of undefined cases.
Nowadays, ChatGPT and other AI coder platforms such as zzzcoder are also pretty useful to get help excel formulas. {Link removed by admin}
Glad things worked out for you.
