Help from Brazil
Good Evening FluidPowerOne.

First of all, remember that you obtained 'H-DATA Sheet' D column using a TEXT formula.

A) "...another formula in this workbook that was solved by using the properties of the cells menu to change the format from number to text. I tried that in this scenario as well and kept getting the #N/A error..."
Answer: If you prefer you can work with Cell Formats too.

Try to do this at: 'Look-up Sheet' F2
a) Change cell format from Number/General to Text
b) Clear the cell

Now, type any code like: 1011001009 and tell me if worked as you desire.

Note:
Particularly, I always prefer use a number as number and a text as text to avoid big problems in the future when the spreadsheet can be bigger and more complex.

Have a nice Day.