Excel – VLOOKUP returns values for some Lookup values but not others

microsoft excelvlookup

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?

enter image description here

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?

Best Answer

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

Related Question