Is there a limit to the character length of a lookup_value?

[MAYNAARD]

I have recently had a series of problems with Vlookup where the lookup_value has a character length >7 digits.
I found a way around the problem with Index and Match, but now wonder is it a known glitch or something wrong with my computer, Excel version v2016?

 

[RoryA]

Re: Is there a limit to length of Lookup_value in Vlookup?

Hi,

The answer is Yes, but the limit is 32,767
So I don't think that's your problem.

It's 255 actually for the lookup value.

 

[steve the fish]

Re: Is there a limit to length of Lookup_value in Vlookup?

This is offset ie it is going to produce whatever is in the cell up 1 row and across to column H from where the lookup value is found:

= INDEX('Student Details'!$H$3:$H$631,MATCH('Population Data'!H37,'Student Details'!A4:A632,1))

so you couldnt use vlookup as it doesnt work like that.

 

[etaf]

should be able to look up values over 7 digits, i often do
255 characters, not sure about the 64bit version

 

[Fluff]

@MAYNAARD
Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).

I have merged both threads.

 

[jtakw]

Re: Is there a limit to length of Lookup_value in Vlookup?

It's 255 actually for the lookup value.

Oops, you're right, I was thinking Cell character limit...

 

[MAYNAARD]

Re: Is there a limit to length of Lookup_value in Vlookup?

Thanks everybody who made useful suggestions.
I spent an hour this morning going over the problem and it seems it all boils down to Column type in three places:
1. in The lookup column
2 In the Looked-up Column, and
3 In the column I sought data fro.

Both the lookup and looked up columns are Student Ids, which are all numerals, Most are 8 digits, but some have fewer digits, and some of them in original form have 6 digits with one leading zero to make up to 7 digits.
Hence the problem as I first reported it -- Vlookup doesn't work with > 7 digits.
I fixed it eventually by converting both lookup rows to Custom set as 00000000.
That worked for 7 digit lookups, but not for 8digit, till I added Numbervalue( ) to the lookup in Index+ Match, and partially in Vlookup.