EXACT function and = is giving different result and my Vlookup is not working because of that

[AZEEM MOHAMED]

Sir

I have a seasoned Vlookup user. However Iam facing an issue.
My lookup value is having some issue
I tried to compare my lookup value with "=" and "Exact"
The "=" gave True and "Exact" function gives false.

Vlookup returns N/A
But Xlookup is giving the correct results.

I have attached an extract of my work
Pls help

 

[bosco_yip]

Try,

1] Your "Sheet AAAAA" have Char(63) found at the head & tail in range B5:B67

2] Select B5 >> copy the character after the last visible character >> Ctrl+"H" >> paste to "Find what" do not enter anything in the "Replace with" >> OK (Please refer to below screenshot replacements result)



Then

3] Go to check your VLOOKUP result in COLUMN I and become all have values,

 

[Debaser]

FWIW, they are actually unicode characters 8237 (at the start) and 8236 (at the end), which are apparently 'left to right override' and 'Pop directional formatting' characters respectively, whatever those may be.

 

[bosco_yip]

FWIW, they are actually unicode characters 8237 (at the start) and 8236 (at the end), which are apparently 'left to right override' and 'Pop directional formatting' characters respectively, whatever those may be.

Thanks Debaser, I just used CODE function to check and returned CHAR(63) in start and end

Regards

 

[Debaser]

I guessed as much. CODE/CHAR don't understand unicode, so you need UNICODE/UNICHAR for that.

 

[AZEEM MOHAMED]

Try,

1] Your "Sheet AAAAA" have Char(63) found at the head & tail in range B5:B67

2] Select B5 >> copy the character after the last visible character >> Ctrl+"H" >> paste to "Find what" do not enter anything in the "Replace with" >> OK (Please refer to below screenshot replacements result)

View attachment 84392

Then

3] Go to check your VLOOKUP result in COLUMN I and become all have values,

View attachment 84393

Thank you so much Bosco
I tried to copy the last character, But i failed.

I checked the length of the string, and as you said it is 2 characters more than the visible characters.
So you are RIGHT.
When i keep the cursor in the text - I fail to copy any trailing or preceding characters
I tried with CLEAN, SUBSTITUTE, TRIM, But failed.

How did you do it
Is there any function to clean it

 

[bosco_yip]

Thank you so much Bosco
.........................
Is there any function to clean it

Sorry, No

...................................
When i keep the cursor in the text - I fail to copy any trailing or preceding characters

Press Ctrl+H >> Select B5 >> Put the cursor in the right side of last character, then press mouse left key and move right, press Ctrl+C >> in "Find what" then click Ctrl+V >>"Replace with", keep blank>> press "Replace All" >> OK

Good luck