+ Reply to Thread
Results 1 to 5 of 5

XLOOKUP Does Not Work With Text-based As Lookup Variable

  1. #1
    Registered User
    Join Date
    05-15-2020
    Location
    North America
    MS-Off Ver
    Office365 (v2209)
    Posts
    29

    XLOOKUP Does Not Work With Text-based As Lookup Variable

    I have a list of phone numbers. I want to be able to lookup the area code for each row and find the corresponding state. So I use LEFT (3) characters on the phone number field to obtain the area code. Then I perform an XLOOPUP on the area code to find the match in the Area Code table. But it retuns #N/A. Does XLOOKUP work with values that are the result of a text formula? If not, is there another type of lookup that would work in this situation?

    I attached an example. Phone numbers are fake. Thanks.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: XLOOKUP Does Not Work With Text-based As Lookup Variable

    Hi,
    Try this in C2:
    =XLOOKUP((B2),TEXT('Area Codes'!$A$1:$A$500,0),'Area Codes'!$B$1:$B$500,"Not Found")

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: XLOOKUP Does Not Work With Text-based As Lookup Variable

    My version of Excel doesn't support XLOOKUP(), so I cannot test that particular function. However, as you note, the output of the LEFT() function is text. The values in column A of your Area Codes lookup table are all numbers. For as long as I can remember, Excel has never been able to recognize that a number stored as text and the corresponding number are the same. So, the text string "615" is not the same thing as the number 615, so Excel's lookup functions (including, apparently, the newest XLOOKUP()) have never been able to match them.

    Solution is to use the VALUE() function or add 0 or multiply by 1 or whatever other strategy you like to get the area codes in Call Data column B to be numbers and not numbers stored as text.
    =VALUE(LEFT(...))
    =LEFT(...)+0
    =--LEFT(...)
    =LEFT(...)*1
    or equivalent.

    ETA -- or, as belinda noted, change the numbers in the lookup table to text strings using something like the TEXT() function.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    05-15-2020
    Location
    North America
    MS-Off Ver
    Office365 (v2209)
    Posts
    29

    Re: XLOOKUP Does Not Work With Text-based As Lookup Variable

    Thanks belinda200, this worked perfectly!

  5. #5
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    office 365 for MAC
    Posts
    3,105

    Re: XLOOKUP Does Not Work With Text-based As Lookup Variable

    you're welcome , SweetBaboo

+ 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. [SOLVED] Xlookup when both the lookup value and lookup array are separated by commas
    By Eades1412 in forum Excel Formulas & Functions
    Replies: 19
    Last Post: 02-08-2022, 04:49 AM
  2. Replies: 7
    Last Post: 12-10-2021, 02:17 PM
  3. Xlookup variable to search for defined by slicer
    By PrimePorkchop in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-30-2021, 05:33 PM
  4. Lookup xLookup or something..
    By xKelly in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-06-2021, 09:24 PM
  5. Replies: 7
    Last Post: 07-16-2021, 11:04 AM
  6. Connection Commnd text / variable based on lookup table
    By Jabba69 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-07-2018, 05:57 AM
  7. [SOLVED] Double Variable Lookup (Think SUMPRODUCT would work)
    By Jay_hl in forum Excel General
    Replies: 9
    Last Post: 05-14-2015, 01:27 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