I am trying to get the individuals units from the sheet and I cant seem to get it working could someone help me out
Your sheet does not work without the external Excel files.
But the routine is failing because you try to look up the values in the second columns of C ("1 in"), too and they can't be found of course.