Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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'. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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'. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I lookup data based on two columns of data | Excel Worksheet Functions | |||
lookup based on a row number | Excel Worksheet Functions | |||
Lookup based on 2 criteria | Excel Worksheet Functions | |||
In Excel 2003 how do you get a table to update based on lookup cr. | Excel Worksheet Functions | |||
Lookup with search range start based on position of last blank lin | Excel Worksheet Functions |