+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    03-22-2020
    Location
    Brisbane, Qld, Aust
    MS-Off Ver
    2016
    Posts
    4

    Post 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

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

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

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

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

    perhaps a sample spreadsheet explain the issues and examples

    OR as its imported does it have ' visible

    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)
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    03-22-2020
    Location
    Brisbane, Qld, Aust
    MS-Off Ver
    2016
    Posts
    4

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

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. XLOOKUP availability?
    By PeteABC123 in forum Excel General
    Replies: 8
    Last Post: 10-22-2020, 06:10 PM
  2. Sum Function with Xlookup
    By sawaccouniting in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 02-07-2020, 10:10 PM
  3. Obtaining Average from Filtered Table
    By bgreeson in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-31-2017, 01:20 PM
  4. Web data downloads with a ' apostrophe prefix
    By markjoyus in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-15-2016, 09:01 PM
  5. Replies: 1
    Last Post: 12-10-2013, 10:31 AM
  6. Obtaining values from a multi-leveled table.
    By Eliat in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-04-2013, 08:11 AM
  7. Can I remove prefix apostrophe
    By swansonwc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-15-2006, 06:03 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1