DocAElstein
Banned user
- Joined
- May 24, 2014
- Messages
- 1,336
Re: VLOOKUP LookUp Value 255 Character limit
Hi,
. Not a big problem. (I have a few (complicated) ideas already). But Just on the off-chance someone knows a simple solution….
. Partly as a learning process and partly in preparation for a project involving big complicated Files and data tables, I am comparing different methods to look up and sort large files.
. Amongst other things I am looking at VLOOKUP. I hit a problem. After a bit of googling and trial and error I realized it was because of a limit in the LookUp Value in the LOOKUP Function of 255 characters.
. So to give an example. In the following spreadsheet you can see I have an error for the pink entry as the look Up value exceeds 255 characters.
. I can think of many complicated ways to overcome this problem such as using temporary columns with truncated values in comparing and looking up etc., but as speed may be important in my final large files I was hoping for a simple “one liner” solution.
. I was thinking along the lines something like of modifying this type of code….
to something like this
. By experimenting I have found that VBA is happy with the Red highlighted modification but does not like the blue highlighted modification.
. Can anyone suggest a one-liner code syntax modification that works?
Thanks
Alan
Hi,
. Not a big problem. (I have a few (complicated) ideas already). But Just on the off-chance someone knows a simple solution….
. Partly as a learning process and partly in preparation for a project involving big complicated Files and data tables, I am comparing different methods to look up and sort large files.
. Amongst other things I am looking at VLOOKUP. I hit a problem. After a bit of googling and trial and error I realized it was because of a limit in the LookUp Value in the LOOKUP Function of 255 characters.
. So to give an example. In the following spreadsheet you can see I have an error for the pink entry as the look Up value exceeds 255 characters.
| Book1 | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Product | ||||
| 2 | Name | ||||
| 3 | *** SOYAsojasource 77/,1/12/6,9/3,7/,6/8,3/ soysoacjap 39/0/3,5/6/ / / /spanish vineger 26/0/,023/,53//// balsamico 87/0/1/16/13/0/,05/ | 1 | |||
| 4 | ErroskiWine | 2 | |||
| 5 | SENF english 170/6,7/6/21//// grill & steak 118/6,3/5,8/8,1//// sweetpickle130/,2/,8/31//// | 3 | |||
| 6 | SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 186/15/9/3,7/ / / /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4//// | #### | |||
| 7 | |||||
| 8 | |||||
| 9 | Look Up | ||||
| 10 | Table | ||||
| 11 | *** SOYAsojasource 77/,1/12/6,9/3,7/,6/8,3/ soysoacjap 39/0/3,5/6/ / / /spanish vineger 26/0/,023/,53//// balsamico 87/0/1/16/13/0/,05/ | 1 | |||
| 12 | SENF english 170/6,7/6/21//// grill & steak 118/6,3/5,8/8,1//// sweetpickle130/,2/,8/31//// | 3 | |||
| 13 | ErroskiWine | 2 | |||
| 14 | SCHMELZKSE/WWSPREAD philadelphia 179/15/7,6/4/,1/,5/,4/ kruter 152/11/7,8/4,8/4,6/,7/,5/ leicht110/4,7/11,5/5/5/,4/,4/ kruter 186/15/9/3,7/ / /brlauch 296/28/5/5/3/1/,7/ bresco 230/19,5/8,2/5,4////frischksekruter 186/15/9/3,7/ / / /brunchkraut/gurk 215/20,5/4,2/3,5/3/,1/,45/cleop 220/20/4,5/6/5/,1/,35/ bresco 230/19,5/8,2/5,4//// | 4 | |||
Tabelle1 | |||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3 | =VLOOKUP(A3,$A$11:$C$14,3,FALSE) | |
| C4 | =VLOOKUP(A4,$A$11:$C$14,3,FALSE) | |
| C5 | =VLOOKUP(A5,$A$11:$C$14,3,FALSE) | |
| C6 | =VLOOKUP(A6,$A$11:$C$14,3,FALSE) | |
. I can think of many complicated ways to overcome this problem such as using temporary columns with truncated values in comparing and looking up etc., but as speed may be important in my final large files I was hoping for a simple “one liner” solution.
. I was thinking along the lines something like of modifying this type of code….
Code:
=VLOOKUP(A6,$A$11:$C$14,3,FALSE)to something like this
Code:
=VLOOKUP([COLOR=#FF0000]LEFT(A6,10)[/COLOR],$A$11:$C$15,[COLOR=#0000CD]LEFT(3,10)[/COLOR],FALSE. By experimenting I have found that VBA is happy with the Red highlighted modification but does not like the blue highlighted modification.
. Can anyone suggest a one-liner code syntax modification that works?
Thanks
Alan
></o
