Forum Discussion
Why is XLOOKUP not returning the value on one sheet but does on another?
Attached is the spreadsheet I am working on. Very simple request. base_data contains a product SKU, fineline code and a few others. The worksheet product-heliumSKU uses a LET statement to take data from base_data and using the fineline code, use it to look up in another spreadsheet (fineline-heliumSKU) to obtain the helium SKU. But alas, it seems to not find the helium SKU, yet the same XLOOKUP does return the helium SKU (see fineline-heliumSKU). There has to be something wrong with the XLOOKUP in the LET statement, just stumped.
NotSoFastEddie This is simply caused by the lookup_value and the lookup_array being different data types. All of the SKU and Fineline Codes on the baseData worksheet are numbers formatted as text, while the Fineline-HeliumSKU worksheet contains actual numeric values. There are many ways to remedy this, but one example would be to change the finelineCode definition in your LET statement from INDEX(data_tbl,,2) to --INDEX(data_tbl,,2). The double-negative sign will force the numbers format as text to become numeric values.
NotSoFastEddie This is simply caused by the lookup_value and the lookup_array being different data types. All of the SKU and Fineline Codes on the baseData worksheet are numbers formatted as text, while the Fineline-HeliumSKU worksheet contains actual numeric values. There are many ways to remedy this, but one example would be to change the finelineCode definition in your LET statement from INDEX(data_tbl,,2) to --INDEX(data_tbl,,2). The double-negative sign will force the numbers format as text to become numeric values.
djclements Thanks again DJ. I did look at the data and I saw it defined as General. I guess I didn't look far enough.
