I have an entry cell (Lets say A1) that is subsequently used for an XLOOKUP. The value is often pasted, it is typically a 7 digit project number. Sometimes there are leading 0's sometimes not. Sometimes it pastes as text, sometimes not. I end up embedding an XLOOKUP into the "not found" parameter as so;
= XLOOKUP(A1,Project[PNum],Project[Description],XLOOKUP(A1+0,Project[PNum],Project[Description],"#NF#",0),0)
There have been a couple gotchas in that, and I recently discovered (and am here looking to confirm) that I can apply the TEXT function to the lookup array. Like
= XLOOKUP(TEXT(A1,"#"),TEXT(Project[PNum],"#"),Project[Description],"#NF#",0)
Technically they are not numeric and this seems to work. I am here however because the text version did not work i.e.
= XLOOKUP(TEXT(A1,"0000000"),TEXT(Project[PNum],"0000000"),Project[Description],"#NF#",0)
My concern is that perhaps the reason the # version works is a fluke of the data sort or such but that it is not actually doing what I think it is.
If there is someone out there with a strong knowledge of how excel treats these array-ish mixed formula's I would appreciate it. In days of yore Excel would have just errored for putting a range in the Text function.
= XLOOKUP(A1,Project[PNum],Project[Description],XLOOKUP(A1+0,Project[PNum],Project[Description],"#NF#",0),0)
There have been a couple gotchas in that, and I recently discovered (and am here looking to confirm) that I can apply the TEXT function to the lookup array. Like
= XLOOKUP(TEXT(A1,"#"),TEXT(Project[PNum],"#"),Project[Description],"#NF#",0)
Technically they are not numeric and this seems to work. I am here however because the text version did not work i.e.
= XLOOKUP(TEXT(A1,"0000000"),TEXT(Project[PNum],"0000000"),Project[Description],"#NF#",0)
My concern is that perhaps the reason the # version works is a fluke of the data sort or such but that it is not actually doing what I think it is.
If there is someone out there with a strong knowledge of how excel treats these array-ish mixed formula's I would appreciate it. In days of yore Excel would have just errored for putting a range in the Text function.