Used xlookup and came back with result #n/a, text fields are not equal

tana

New Member
Joined
Jan 22, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi, can anyone give me some help please?

I have the following downloaded data from a financial system:

1611358781585.png


When I tried to pull in the number column from the matched description in another table, I received #n/a:

1611358871974.png

I also realized the text fields are not equal although they are the exact same text.

1611359054744.png


I have checked the length of both text and length is the same 51 characters.

Can you please help me? Thanks for your help in advance.

Anna
 

Attachments

  • 1611358574547.png
    1611358574547.png
    23.3 KB · Views: 2
  • 1611358661303.png
    1611358661303.png
    28.3 KB · Views: 2

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

Can you please upload the sheet.

Also, to test delete the description in C12 cell in downloaded data. Rewrite the value. Check if it's working.



Thanks,
Saurabh
 

tana

New Member
Joined
Jan 22, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi, Saurabh:

I am sorry I don't know how to upload the sheet. I tried to download XL2BB and install to my excel but it did not show. Is there any other way I can share the file with you so you can help me? Thanks,

Anna
 

tana

New Member
Joined
Jan 22, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi, Saurabh:

I figured it out now. Here is the data:

Book1.xlsx
AB
1NumberDescription
2214410182021 Project Intiation - Work from home connectivity and security improvement (renamed from Internet Security)
3214410172021 Project Intiation - Software Asset Management Optimization
4214410162021 Project Intiation - Azure West migration Project Part II
5214410152021 Project Intiation - Third party risk - ServiceNow
6214410142021 Project Intiation - Meeting AV & Collaboration Technology Refresh-2021
7214410132021 Project Intiation - Device Refresh & Image Standardization-2021
8214410122021 Project Intiation - Threat Prevention, URL Filtering and DNS Security
9214410112021 Project Intiation - System Lifecycle Project
10214410102021 Project Intiation - Penetration Testing and Vulnerability Assessment
11214409992021 Project Intiation - Network Lifecycle Project
12214409982021 Project Intiation - Data Loss Prevention (DLP)
Sheet1


Book1.xlsx
AB
1WO Descrip. Hardcoded
22021 Project Intiation - Data Loss Prevention (DLP)#N/A
32021 Project Intiation - Network Lifecycle Project
42021 Project Intiation - Penetration Testing and Vulnerability Assessment
52021 Project Intiation - System Lifecycle Project
62021 Project Intiation - Threat Prevention, URL Filtering and DNS Security
72021 Project Intiation - Device Refresh & Image Standardization-2021
82021 Project Intiation - Meeting AV & Collaboration Technology Refresh-2021
92021 Project Intiation - Third party risk - ServiceNow
102021 Project Intiation - Azure West migration Project Part II
112021 Project Intiation - Software Asset Management Optimization
122021 Project Intiation - Work from home connectivity and security improvement (renamed from Internet Security)
Sheet2
Cell Formulas
RangeFormula
B2B2=XLOOKUP($A2,Sheet1!B:B,Sheet1!A:A)
 

tana

New Member
Joined
Jan 22, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi,

I have figured out portion of it. There are still two of them I could not figure it out, can you please help me? Thanks in advance.

Xlookup #NA Issue.xlsx
ABC
1NumberDescriptionFormula Fix
2214410182021 Project Intiation - Work from home connectivity and security improvement (renamed from Internet Security)2021 Project Intiation - Work from home connectivity and security improvement (renamed from Internet Security)
3214410172021 Project Intiation - Software Asset Management Optimization2021 Project Intiation - Software Asset Management Optimization
4214410162021 Project Intiation - Azure West migration Project Part II2021 Project Intiation - Azure West migration Project Part II
5214410152021 Project Intiation - Third party risk - ServiceNow2021 Project Intiation - Third party risk - ServiceNow
6214410142021 Project Intiation - Meeting AV & Collaboration Technology Refresh-20212021 Project Intiation - Meeting AV & Collaboration Technology Refresh-2021
7214410132021 Project Intiation - Device Refresh & Image Standardization-20212021 Project Intiation - Device Refresh & Image Standardization-2021
8214410122021 Project Intiation - Threat Prevention, URL Filtering and DNS Security2021 Project Intiation - Threat Prevention, URL Filtering and DNS Security
9214410112021 Project Intiation - System Lifecycle Project2021 Project Intiation - System Lifecycle Project
10214410102021 Project Intiation - Penetration Testing and Vulnerability Assessment2021 Project Intiation - Penetration Testing and Vulnerability Assessment
11214409992021 Project Intiation - Network Lifecycle Project2021 Project Intiation - Network Lifecycle Project
12214409982021 Project Intiation - Data Loss Prevention (DLP)2021 Project Intiation - Data Loss Prevention (DLP)
Sheet1
Cell Formulas
RangeFormula
C2:C12C2=SUBSTITUTE($B2,CHAR(160)," ")


Xlookup #NA Issue.xlsx
AB
1WO Descrip. Hardcodedxlookup
22021 Project Intiation - Data Loss Prevention (DLP)21440998
32021 Project Intiation - Network Lifecycle Project21440999
42021 Project Intiation - Penetration Testing and Vulnerability Assessment21441010
52021 Project Intiation - System Lifecycle Project21441011
62021 Project Intiation - Threat Prevention, URL Filtering and DNS Security#N/A
72021 Project Intiation - Device Refresh & Image Standardization-202121441013
82021 Project Intiation - Meeting AV & Collaboration Technology Refresh-202121441014
92021 Project Intiation - Third party risk - ServiceNow21441015
102021 Project Intiation - Azure West migration Project Part II21441016
112021 Project Intiation - Software Asset Management Optimization21441017
122021 Project Intiation - Work from home connectivity and security improvement (renamed from Internet Security)#N/A
Sheet2
Cell Formulas
RangeFormula
B2:B12B2=XLOOKUP($A2,Sheet1!C:C,Sheet1!A:A)


Thanks, appreciated.
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Hi,

It's strange. I copied the same data in my sheet and it's working perfectly fine. (See images)
 

Attachments

  • xlookup.JPG
    xlookup.JPG
    83.3 KB · Views: 3
  • xlookup2.JPG
    xlookup2.JPG
    81.7 KB · Views: 3
Solution

tana

New Member
Joined
Jan 22, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hi,

Thanks for helping me.

Yes, that is super strange and weird. I don't know how it happened. After reading your message, I tried to copy my original data and tested it again in a new workbook. It came back with the same two #N/As. And then I tried to copy the date from my above post, it does work perfectly. I am wondering if XL2BB filter something out. Everyday is new learning experience. Haha.........

Thanks,

Anna
 

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Yes, its always a new learning...But its good that the problem is resolved.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,768
Messages
5,626,778
Members
416,202
Latest member
donya ba

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
Top