Menu
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?
679
Problem: The lookup_value argument is more than 255 characters. Solution: Shorten the value, or use a combination of INDEX and MATCH functions as a workaround. This is an array formula.
To overcome this restriction one should use an OFFSET function instead of the VLOOKUP one. The given function consists of three obligatory elements: reference, rows and cols. OFFSET returns a value of a cell which is located in X rows (rows) and Y columns (cols) from a specified cell (reference).
With large sets of data, exact match VLOOKUP can be painfully slow, but you can make VLOOKUP lightening fast by using two VLOOKUPS, as explained below. Notes: If you have a smaller set of data, this approach is overkill. Only use it with large data sets when speed really counts.
Lookup value is longer than 255 characters One of the limits of VLOOKUP function is the limit of lookup value. The maximum length can be 255 characters. Therefore, if it's longer than 255 characters, the function will return an #VALUE! error.
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
64
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
