Forum Discussion
XLOOKUP search w/multiple output
Much the same as NikolinoDE
=IF(ISTEXT(OnChNum), "("&OnChNum&")", XLOOKUP(part, oldNumber, OnChList))
=IF(
ISTEXT(OnChNum),
XLOOKUP(OnChNum, OnChList, description),
XLOOKUP(part, oldNumber, description)
)The only other thing I would suggest is that if both the OnCh and part numbers are present you use conditional formatting to hide the part number.
The conditional format would test the OnCh entry but apply number formatting of the form "---" to the part number entry.
Hi Peter, you might consider taking another look at how ISTEXT() behaves within your formula.
ISTEXT() checks specifically whether a cell contains text — it doesn’t evaluate whether the cell contains a valid lookup value. If someone enters a numeric OnCh number (which yours appear to be), ISTEXT() will return FALSE, even though the entry itself is entirely valid.
For example, if OnChNum = 1010 (a number), ISTEXT(OnChNum) evaluates to FALSE. In that case, the formula would not continue with the lookup, even though a correct value has been provided.
It may therefore be helpful to base the condition on whether a value is present rather than whether it is text. This keeps your overall logic intact while making the behavior more flexible for different input types.
True. I started with NOT(ISBLANK(...)) and was over-hasty with the refactoring!
