Lookup based on fractional value
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. |
Lookup based on fractional value
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'. |
Lookup based on fractional value
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'. |
All times are GMT +1. The time now is 06:41 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com