SOLVED

Why is XLOOKUP not returning the value on one sheet but does on another?

Brass Contributor

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.

2 Replies
best response confirmed by NotSoFastEddie (Brass Contributor)
Solution

@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.

1 best response

Accepted Solutions
best response confirmed by NotSoFastEddie (Brass Contributor)
Solution

@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.

View solution in original post