Odds are that Monarch is detecting, rightly or wrongly, that the Product field in the external Excel file is Character. In a perfect world, we'd address the root cause of that issue.
But we can take advantage of Monarch's flexibility and instead take a quicker solution. Add a calculated field to convert your Product to be a character field (CharProduct), and then perform the lookup using CharProduct instead of Product.
The formula for CharProduct is simply:
If some of the lookups fail to retrieve proper values, it'll likely be due to extra trailing or leading spaces in the Product field in the Excel file. Those would have caused Monarch to believe that the field type was Character. It scans the first 250 rows of the external table to determine the field type, so any problems will be hiding in that region.
Thank You for your response.
After reading your response, I opened the Excel file to review the format for the Product Number field. What I found one line below the final Product Number record was a "small box". I copied a blank cell over the "small box" , on a whim, and resaved the Excel file.
The External Lookup worked. Not sure what I did, but I'll take luck anytime.
I will be sure to keep your response handy, if I encounter this problem again.
Thank you again,