克服Excel Vlookup功能限制为256个字符的方法 [英] Way to overcome Excel Vlookup function limit of 256 characters
问题描述
当我尝试使用示例字符串进行VLookup时,我可以在匹配的时候获得结果小于256个字符的行。对于大于256个字符的行,它返回一个'#N / A'。
有没有办法使用Vlookup或在Excel中使用其他内置函数我可以克服这个限制?
如果你这样使用VLOOKUP
= VLOOKUP(A2,D2:Z10,3,FALSE)
ie在D2:D10中查找A2,然后从F2:F10返回一个结果,然后尝试这个公式。
= INDEX(F2:F10,MATCH TRUE,INDEX(D2:D10 = A2,0),0))
根据需要更改范围
编辑
我在这里嘲笑了一个示例 - A2:A10中的值与G2:G10相同但是以不同的顺序。这些值中的每一个的长度显示在列B中,列C中的VLOOKUP在col A值> 255个字符上失败,但是在所有情况下,col D中的INDEX / MATCH公式都可以工作
https://www.dropbox.com/s/fe0sb6bkl3phqdr/vlookup.xls
I have a excel array with multiple values. Some are less than 256 characters and some have a length greater than 256.
When I tried to do a VLookup using a sample string, I can get results when it matches the rows with less than 256 characters. For rows greater that 256 characters, it returns a '#N/A'.
Is there a way of using Vlookup or by using some other in-built function in Excel that I can overcome this limit?
If you are using VLOOKUP like this
=VLOOKUP(A2,D2:Z10,3,FALSE)
i.e. looking up A2 in D2:D10 and returning a result from F2:F10 then try this formula instead
=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))
change ranges as required
Edit:
I mocked up a sample here - values in A2:A10 are the same as G2:G10 but in a different order. The length of each of those values is shown in column B, the VLOOKUP in column C fails on col A values > 255 chars but the INDEX/MATCH formula in col D works in all cases
https://www.dropbox.com/s/fe0sb6bkl3phqdr/vlookup.xls
这篇关于克服Excel Vlookup功能限制为256个字符的方法的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!