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

[tana]

Hi, can anyone give me some help please?

I have the following downloaded data from a financial system:

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

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

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

 

[Saurabhj]

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]

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]

Hi, Saurabh:

I figured it out now. Here is the data:

Book1.xlsx
	A	B
1	Number	Description
2	21441018	2021 Project Intiation - Work from home connectivity and security improvement (renamed from Internet Security)
3	21441017	2021 Project Intiation - Software Asset Management Optimization
4	21441016	2021 Project Intiation - Azure West migration Project Part II
5	21441015	2021 Project Intiation - Third party risk - ServiceNow
6	21441014	2021 Project Intiation - Meeting AV & Collaboration Technology Refresh-2021
7	21441013	2021 Project Intiation - Device Refresh & Image Standardization-2021
8	21441012	2021 Project Intiation - Threat Prevention, URL Filtering and DNS Security
9	21441011	2021 Project Intiation - System Lifecycle Project
10	21441010	2021 Project Intiation - Penetration Testing and Vulnerability Assessment
11	21440999	2021 Project Intiation - Network Lifecycle Project
12	21440998	2021 Project Intiation - Data Loss Prevention (DLP)
Sheet1

Book1.xlsx
	A	B
1	WO Descrip. Hardcoded
2	2021 Project Intiation - Data Loss Prevention (DLP)	#N/A
3	2021 Project Intiation - Network Lifecycle Project
4	2021 Project Intiation - Penetration Testing and Vulnerability Assessment
5	2021 Project Intiation - System Lifecycle Project
6	2021 Project Intiation - Threat Prevention, URL Filtering and DNS Security
7	2021 Project Intiation - Device Refresh & Image Standardization-2021
8	2021 Project Intiation - Meeting AV & Collaboration Technology Refresh-2021
9	2021 Project Intiation - Third party risk - ServiceNow
10	2021 Project Intiation - Azure West migration Project Part II
11	2021 Project Intiation - Software Asset Management Optimization
12	2021 Project Intiation - Work from home connectivity and security improvement (renamed from Internet Security)
Sheet2

Cell Formulas
Range	Formula
B2	B2	=XLOOKUP($A2,Sheet1!B:B,Sheet1!A:A)

 

[tana]

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
	A	B	C
1	Number	Description	Formula Fix
2	21441018	2021 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)
3	21441017	2021 Project Intiation - Software Asset Management Optimization	2021 Project Intiation - Software Asset Management Optimization
4	21441016	2021 Project Intiation - Azure West migration Project Part II	2021 Project Intiation - Azure West migration Project Part II
5	21441015	2021 Project Intiation - Third party risk - ServiceNow	2021 Project Intiation - Third party risk - ServiceNow
6	21441014	2021 Project Intiation - Meeting AV & Collaboration Technology Refresh-2021	2021 Project Intiation - Meeting AV & Collaboration Technology Refresh-2021
7	21441013	2021 Project Intiation - Device Refresh & Image Standardization-2021	2021 Project Intiation - Device Refresh & Image Standardization-2021
8	21441012	2021 Project Intiation - Threat Prevention, URL Filtering and DNS Security	2021 Project Intiation - Threat Prevention, URL Filtering and DNS Security
9	21441011	2021 Project Intiation - System Lifecycle Project	2021 Project Intiation - System Lifecycle Project
10	21441010	2021 Project Intiation - Penetration Testing and Vulnerability Assessment	2021 Project Intiation - Penetration Testing and Vulnerability Assessment
11	21440999	2021 Project Intiation - Network Lifecycle Project	2021 Project Intiation - Network Lifecycle Project
12	21440998	2021 Project Intiation - Data Loss Prevention (DLP)	2021 Project Intiation - Data Loss Prevention (DLP)
Sheet1

Cell Formulas
Range	Formula
C2:C12	C2	=SUBSTITUTE($B2,CHAR(160)," ")

Xlookup #NA Issue.xlsx
	A	B
1	WO Descrip. Hardcoded	xlookup
2	2021 Project Intiation - Data Loss Prevention (DLP)	21440998
3	2021 Project Intiation - Network Lifecycle Project	21440999
4	2021 Project Intiation - Penetration Testing and Vulnerability Assessment	21441010
5	2021 Project Intiation - System Lifecycle Project	21441011
6	2021 Project Intiation - Threat Prevention, URL Filtering and DNS Security	#N/A
7	2021 Project Intiation - Device Refresh & Image Standardization-2021	21441013
8	2021 Project Intiation - Meeting AV & Collaboration Technology Refresh-2021	21441014
9	2021 Project Intiation - Third party risk - ServiceNow	21441015
10	2021 Project Intiation - Azure West migration Project Part II	21441016
11	2021 Project Intiation - Software Asset Management Optimization	21441017
12	2021 Project Intiation - Work from home connectivity and security improvement (renamed from Internet Security)	#N/A
Sheet2

Cell Formulas
Range	Formula
B2:B12	B2	=XLOOKUP($A2,Sheet1!C:C,Sheet1!A:A)

Thanks, appreciated.

 

[Saurabhj]

Hi,

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

 

[tana]

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]

Yes, its always a new learning...But its good that the problem is resolved.