Home |
Search |
Today's Posts |
#1
|
|||
|
|||
vlookup on large text in cells
Any help with the following would be greatly appreciated -
I'm trying to do a vlookup whereby the cell that contains the data to be searched and the column that will be searched, contain large amounts of text. Having realised large amounts of text seem to be the problem (as vlookup returned correct answers when cells had < 200 characters in them) I tried to use a formular to lookup only the first 100 characters in the cells: =VLOOKUP(LEFT(B1,200)&"*",'[Regs2.xls]Agr-Tra'!E4:N1800,3,0) This formular returned the wrong data for the cells with < 200 characters and #N/As for the cells with 200 characters. Is it therefore possible to do a vlookup on a large amount of text contained within both the column to be searched and cell to be matched? Many thanks in advance for any help. Gus |
#2
|
|||
|
|||
Gus wrote...
Any help with the following would be greatly appreciated - I'm trying to do a vlookup whereby the cell that contains the data to be searched and the column that will be searched, contain large amounts of text. Having realised large amounts of text seem to be the problem (as vlookup returned correct answers when cells had < 200 characters in them) I tried to use a formular to lookup only the first 100 characters in the cells: =VLOOKUP(LEFT(B1,200)&"*",'[Regs2.xls]Agr-Tra'!E4:N1800,3,0) This formular returned the wrong data for the cells with < 200 characters and #N/As for the cells with 200 characters. Is it therefore possible to do a vlookup on a large amount of text contained within both the column to be searched and cell to be matched? .... The short answer is NO it's not possible to use VLOOKUP with very long text strings. A little experimentation would show that VLOOKUP works up to 255 characters in its 1st argument. E.g., if I enter the following A1: x B1: 1 A2: =REPT("x",D2) B2: 2 D2: 255 A4: =VLOOKUP(A2,A1:B2,2,0) the VLOOKUP call in cell A4 returns 2, but if I then increase D2 to 256, the VLOOKUP call in A4 returns #VALUE!. I'd need to replace the 1st argument to VLOOKUP with =REPT("x",254)&"*" in order to get a match using the longest possible exact leftmost substring. So either use LEFT(<YourSearchStringHere,254)&"*" or "*"&MID(<YourSearchStringHere,<YourStartPositionH ere,254)&"*" if partial matching would work, or figure out some way of indexing or condensing these long strings. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
vlookup on large text in cells
Harlan, et al,
Is it possible to do a partial match between text in two separate columns? For example Col A Col B Hawaii Sports Inc. Hawaii Sprts A formula in Col C would return Col A text: "Hawaii Sports Inc." Because any text in Col B matches any text in Col A -- in this case the matching text is "Hawaii" Thanks very much. "Harlan Grove" wrote: Gus wrote... Any help with the following would be greatly appreciated - I'm trying to do a vlookup whereby the cell that contains the data to be searched and the column that will be searched, contain large amounts of text. Having realised large amounts of text seem to be the problem (as vlookup returned correct answers when cells had < 200 characters in them) I tried to use a formular to lookup only the first 100 characters in the cells: =VLOOKUP(LEFT(B1,200)&"*",'[Regs2.xls]Agr-Tra'!E4:N1800,3,0) This formular returned the wrong data for the cells with < 200 characters and #N/As for the cells with 200 characters. Is it therefore possible to do a vlookup on a large amount of text contained within both the column to be searched and cell to be matched? .... The short answer is NO it's not possible to use VLOOKUP with very long text strings. A little experimentation would show that VLOOKUP works up to 255 characters in its 1st argument. E.g., if I enter the following A1: x B1: 1 A2: =REPT("x",D2) B2: 2 D2: 255 A4: =VLOOKUP(A2,A1:B2,2,0) the VLOOKUP call in cell A4 returns 2, but if I then increase D2 to 256, the VLOOKUP call in A4 returns #VALUE!. I'd need to replace the 1st argument to VLOOKUP with =REPT("x",254)&"*" in order to get a match using the longest possible exact leftmost substring. So either use LEFT(<YourSearchStringHere,254)&"*" or "*"&MID(<YourSearchStringHere,<YourStartPositionH ere,254)&"*" if partial matching would work, or figure out some way of indexing or condensing these long strings. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to combine text from multiple cells? | Excel Worksheet Functions | |||
retrieve text from merged cells | Excel Worksheet Functions | |||
Cells formated as numbers are calculating like text | Excel Discussion (Misc queries) | |||
Macro or Formula to remove Text from Cells | Excel Worksheet Functions | |||
Referencing cells text output if it meets specific conditions | Excel Worksheet Functions |