interesting vlookup result

[ajm]

just did a vlookup that returned a value error. wasn't expecting that so checked my ranges and referencing. all good. to check, did a quick "this cell = that cell" and the result is true. so the lookup value exists in the lookup array. the only thing i can think it might be is that the lookup value in a vlookup is limited to a certain number of characters. seems to be happening to those lookup values with more than 300 characters in the cell. might even be limited below that number. that is just the shortest of the values i am using. any suggestions?

 

[kweaver]

My understanding is the maximum length can be 255 characters of the value in a VLOOKUP as well as an XLOOKUP, so that's probably your issue.

 

[bebo021999]

How about partial VLOOKUP? I mean you can VLOOKUP 255 or less characters only, not 300
For example, below is for 8 characters:

Book1
	A	B	C	D	E
1	abcdefghzzzz	5
2
3			addd	1	4
4			abcdefghijklyyyy	2	5
5			ssss	3	6
Sheet2

Cell Formulas
Range	Formula
B1	B1	=VLOOKUP(LEFT(A1,8),CHOOSE({1,2},LEFT(C3:C5,8),E3:E5),2)
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Both Xlookup & Xmatch can handle more than 255 characters, so you would need to use one of those.

 

[ajm]

Both Xlookup & Xmatch can handle more than 255 characters, so you would need to use one of those.

i shall try them and see.as mentioned above, i had thought there was a character limit but couldn't recall whether it had been addressed more recently (thanks Kweaver). I had also tried using partial strings as the lookup value but am dealing with multiple items with very similar codes which can be one or two digits off each other. (thanks bebo)

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[kweaver]

Fluff: other than you being just plain smart, where is there a spec on Xlookup and Xmatch that says you can use more than 255 in the look-up?

 

[Fluff]

where is there a spec on Xlookup and Xmatch that says you can use more than 255 in the look-up?

No idea, I just discovered by accident that xmatch could & so tested xlookup which was the same.

 

[RachelN76]

Thank you SO much for this. I apologise and I've just cluttered up the forum with exactly the same issue, except that my cell values are only 10 characters long.
Have never heard of XLOOKUP before, but just tried it and it works.

Thank you