7

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?

Chicken Sandwich No Pickles
  • 381
  • 1
  • 2
  • 14

1 Answers1

19

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

Chicken Sandwich No Pickles
  • 381
  • 1
  • 2
  • 14
  • 3
    +1 For finding out what was the issue. – Reddy Lutonadio Sep 15 '20 at 18:07
  • Beat me to it by 10 min. I run into this all the time when using a concatenated key field. Always have to wrap the source and lookup with `LEFT(...,255)` when doing indexing. – Alex M Sep 15 '20 at 18:15
  • 7
    Once you start having to do work-arounds to Excel size limits, it's time to start question your decision to use Excel in the first place. – Acccumulation Sep 16 '20 at 05:42
  • 1
    @Acccumulation No, that's the moment you start splitting things up or move to VBA. Sometimes Excel is still the best tool for the job. Not from a programming-perspective, but from a workplace-perspective. – Mast Sep 16 '20 at 07:02
  • @AlexM Doing calculations on the values that VLOOKUP is supposed to lookup in is screaming performance nightmare. - Not to mention unmaintainable. – I'm with Monica Sep 16 '20 at 09:19
  • then you use readxl with R – qwr Sep 16 '20 at 16:38
  • @I'mwithMonica I have to do something like this about a handful of times a month and I just create adhoc key fields on both ends and use those, so there aren't calculations inside the lookup (which is `INDEX MATCH` not `VLOOKUP` obviously). Anyway I'm only typically dealing with hundreds of records when doing _this_ kind of work so performance isn't high among my concerns – Alex M Sep 16 '20 at 17:38
  • 1
    XLOOKUP seems to bypass the 255 limit without any problem and it's simple to use. I edited my answer to reflect this for everyone's sake in the future. – Chicken Sandwich No Pickles Sep 16 '20 at 17:43
  • @AlexM For me it is usually a number of records in the high thousands and a lookup table out of my control, thus effectively immutable... ;) – I'm with Monica Sep 17 '20 at 06:49