Managed to replicate this with JMP16.0. JMP will return #NAME? for cells with XLOOKUP formula. Attached is Excel sheet with three different formulas: XLOOKUP, SUM and LOOKUP in which SUM and LOOKUP work fine. I would guess that this will be fixed in future version of JMP (but no idea in which).
For a workaround you could recalculate the values after importing the data from excel with JMP formula.
![jthi_0-1627915457081.png jthi_0-1627915457081.png](https://community.jmp.com/t5/image/serverpage/image-id/34686iF073323F90AAA1AA/image-size/medium?v=v2&px=400)
![jthi_3-1627915526043.png jthi_3-1627915526043.png](https://community.jmp.com/t5/image/serverpage/image-id/34689iAADA2214EA6E634D/image-size/medium?v=v2&px=400)
![jthi_4-1627915532967.png jthi_4-1627915532967.png](https://community.jmp.com/t5/image/serverpage/image-id/34690iCBEC40725015B70A/image-size/medium?v=v2&px=400)
-Jarmo