VLOOKUP returns values for some Lookup values but not others

Question

I'm not sure why VLOOKUP isn't returning the value.

I have confirmed that I'm using the VLOOKUP correctly and that both values 1 and 2 are a match (no leading/trailing values, etc).

Does anyone know what I could be doing wrong?

Edit:

I am using Microsoft 365

Vlookup Formula I am using:

=VLOOKUP(A3,J:K,2,0)

If I copy A3 into J3, I still get an error.

I believe this has something to do with the A3 values, not the vlookup formula and not the lookup value.

What I tried doing:

I tried Editing parts of A3 and then making J3 use a formula where J3 = A3 and it worked, but it doesn't work if I use the original value of A3.

It's definitely something with the value, but what could it be?

When A3 is equal to this value:

generate Rebate accruals, Project quotes, Marketing activities, Call lists for Sales; | create Ship&Debit conditions; | ensure correct SKU-customer-mapping (Backends, Standard Pricing, Excon Pricing, DND, HotSKU Management), includes creation of SAP

It works fine.

But when A3 is equal to this value:

generate Rebate accruals, Project quotes, Marketing activities, Call lists for Sales; | create Ship&Debit conditions; | ensure correct SKU-customer-mapping (Backends, Standard Pricing, Excon Pricing, DND, HotSKU Management), includes creation of SAP upload templates from vendor approvals

It doesn't...maybe there is some sort of hidden character that I'm not aware of?

Answer 1

255 Character limit on VLOOKUP

VLookup has a character limit, as soon as you go over 255 characters, it won't work for what you are trying to do.

If your lookup value has more than 255 characters, you will have to find another solution such as using the LEFT function, RIGHT, manually doing it, etc.

This was my problem.

Edit:

It seems this is another solution and the character limit isn't a problem: XLOOKUP