XLOOKUP obtaining a value from another table that has an apostrophe prefix in the cell

Hi

I have a spreadsheet that imports data using an OBDC driver and creates a table. I cannot modify the data format that it comes in during the refresh etc.

When the data in that table for Unit ID is displayed it looks like 0605 but in fact is '0605

I am seriously trying to get a cell in a worksheet that does not have an ' prefix 0605 to match a cell in the table that does '0605

I have looked for 5 days to find an answer but have failed, so that's why I am here.

The formula I am using is:

=XLOOKUP(B2,SO_DataSet[Unit ID],SO_DataSet[Sales Order Number],"No SO",0,1)

So I want to perform a match 0605 to a modified 0605 (now missing ' ) and then pull from the table the Sales Order Number

Would really appreciate the assistance, Greg

Hi

B2 = 605 as its a number and will remove the leading 0
is that correct ? > B2 is formatted as text and is 0605 in the cell
OR
what is in B2 > 0605

how is 0605 entered
is it formatted as text > Yes
if so then it should still work
as the ' changes to text
and should work

> In the table SO_Dataset[Unit ID] its imported as '0605

perhaps a sample spreadsheet explain the issues and examples

OR as its imported does it have ' visible > Yes Its also a text field

otherwise
=XLOOKUP(B2,SO_DataSet[Unit ID],SO_DataSet[Sales Order Number],"No SO",0,1)

=XLOOKUP("'"&B2,SO_DataSet[Unit ID],SO_DataSet[Sales Order Number],"No SO",0,1)

> I am trying this formulae at the moment and it seems to work:

> =XLOOKUP(B2,CLEAN(SO_DataSet[Unit ID]),SO_DataSet[Sales Order Number],"No SO",0,1)


I will advise if it works as expected, thanks for the quick response, Greg