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?

 

[MAYNAARD]

Is there a limit to length of Lookup_value in Vlookup?

I have recently had problems with Vlookup where the lookup_Value was greater than 7 characters. Returned #N/A when the data existed in the look up table.
Largely resolved using Index and Match
Is this a known issue in Excel 2016, or is it likely a problem in my computer?

 

[shaowu459]

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

That should not be bugs for VLOOKUP, will you please show your formula? Both the VLOOKUP and Index+Match.

 

[jtakw]

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.

 

[MAYNAARD]

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

That should not be bugs for VLOOKUP, will you please show your formula? Both the VLOOKUP and Index+Match.

In its simplest form, I have 2 Sheets in a single Excel instance.

One sheet holds Population data 601 rows deep by A:Q wide.

The other holds Student Details 632 deep by A:V wide.

The goal is to import into the Popdata sheet some details from the Details sheet, using Vlookup. Done it a thousand times without a problem.

The lookup key is StudentID Columns in General field types sorted A:Z which are all Numerals of 6, 7 or 8 digit length.

Vlookup in Column R of Popdata seeking any corresponding data from StuDet, works as usual when the llength of the Lookup_value and Target data are less than 8 character long, but either returns #N/A or leaves the formula, even though the data is available.

Using Index + Match however does work.

 

[MARK858]

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

You neglected to post the 2 formula as requested by shaowu459

will you please show your formula? Both the VLOOKUP and Index+Match.

 

[MAYNAARD]

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

You neglected to post the 2 formula as requested by shaowu459

=VLOOKUP($H33,'Student Details'!A$3:V$631,1,0)

=INDEX('Student Details'!$H$#:$H^#!,MATCH("Population Data'!H38,Student Details'!A5:A633.1))

 

[shaowu459]

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

=VLOOKUP($H33,'Student Details'!A$3:V$631,1,0)

=INDEX('Student Details'!$H$#:$H^#!,MATCH("Population Data'!H38,Student Details'!A5:A633.1))

The form of the formula =VLOOKUP($H33,'Student Details'!A$3:V$631,1,0) is right, but the 3rd parameter may not be 1, it should be 8 which means you want values from column H. Since you get errors, please check the data type of H33 and 'Student Details'!A$3:A$633, make sure they are all numbers or numbers stored as text.

There are errors in the formula =INDEX('Student Details'!$H$#:$H^#!,MATCH("Population Data'!H38,Student Details'!A5:A633.1)), the 3rd parameter of MATCH you can not use 1 for exact match, use 1 will get an answer but it is very likely not the results you want.

 

[MAYNAARD]

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

=VLOOKUP($H33,'Student Details'!A$3:V$631,1,0)

=INDEX('Student Details'!$H$#:$H^#!,MATCH("Population Data'!H38,Student Details'!A5:A633.1))

Oops

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

 

[shaowu459]

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

The last parameter for Match is 1, you should use 0. You the formula returns a value, but it is very likely not the result you expected, because Match is not workd as exactly match.