Lookup based on fractional value

[Ted Metro]

I have a list of companies A1:A100, and I have a table b1:c5000 that has
companies and industries.

The list in the A column has slight differenations is spelling. For example
"DaimlerChrysler" and "Daimler Chrylser" are two of the values. In the big
table the entry is Daimler Chrysler USA, and the industry is Automotive.

I'd like to use a vlookup or index/match combination to append the industry
to the companies in column A. Because there are slight differences in the
spellings I'd like to have a formula take the first 6 letters of A1, and see
if they are found in any cell b1:b5000, and if there is a match to populate
the cell with the corresponding value in column c, the industry value.

So it's essentially a vlookup, but using a fraction of the lookup value.

[Harlan Grove]

Ted Metro wrote...
>I have a list of companies A1:A100, and I have a table b1:c5000 that has
>companies and industries.
....

In order to put the results in B1:B100 next to A1:A100, I'll assume the
other table is in G1:H5000.

>I'd like to use a vlookup or index/match combination to append the industry
>to the companies in column A. Because there are slight differences in the
>spellings I'd like to have a formula take the first 6 letters of A1, and see
>if they are found in any cell b1:b5000, and if there is a match to populate
>the cell with the corresponding value in column c, the industry value.

B1:
=VLOOKUP(LEFT(A1,6)&"*",$G$1:$H$5000,2,0)

Good luck with corresponding entries like 'X.Y.Z Corp.' and 'The XYZ
Corporation'.

[Ted Metro]

Thank you so much Harlan, and you hit on my other problem, which I didn't
mention b/c I figured it would be too hard to capture everything with one
formula.



"Harlan Grove" wrote:

> Ted Metro wrote...
> >I have a list of companies A1:A100, and I have a table b1:c5000 that has
> >companies and industries.
> ....
>
> In order to put the results in B1:B100 next to A1:A100, I'll assume the
> other table is in G1:H5000.
>
> >I'd like to use a vlookup or index/match combination to append the industry
> >to the companies in column A. Because there are slight differences in the
> >spellings I'd like to have a formula take the first 6 letters of A1, and see
> >if they are found in any cell b1:b5000, and if there is a match to populate
> >the cell with the corresponding value in column c, the industry value.
>
> B1:
> =VLOOKUP(LEFT(A1,6)&"*",$G$1:$H$5000,2,0)
>
> Good luck with corresponding entries like 'X.Y.Z Corp.' and 'The XYZ
> Corporation'.
>
>