vlookup on large text in cells

[Guest]

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

 

[Harlan Grove]

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>,<YourStartPositionHere>,254)&"*"

if partial matching would work, or figure out some way of indexing or
condensing these long strings.

 

[Guest]

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.

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>,<YourStartPositionHere>,254)&"*"

if partial matching would work, or figure out some way of indexing or
condensing these long strings.