Nested XLOOKUP to resolve Numbers as text OR can I apply TEXT()

dircur

New Member
Joined
Nov 11, 2008
Messages
22
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.
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Threads
1,223,886
Messages
6,175,193
Members
452,616
Latest member
intern444

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top