Partial VlookUp

  • I would like to do "Partitional VlookUp".
    What I mean, for example in one table I have following items:
    ON
    NATIONAL
    MICRO


    in other table I have:
    ON SEMICONDUCTOR
    NATIONAL SEMICONDUCTOR
    MICRO SEMICONDUCTOR


    I would like to link between same items (to look part of the word).

  • Re: Partial VlookUp


    I would set up another column in table 2, that trims it down to the first part only... then use that for lookup...


    i.e., your table 2


    ON SEMICONDUCTOR
    NATIONAL SEMICONDUCTOR
    MICRO SEMICONDUCTOR


    is in G1:G3


    in H1 place =TRIM(LEFT(G1,FIND(" ",G1,1))) and copy down...


    Then in table 1 you could use


    =VLOOKUP(A1,$H$1:$I$3,2,FALSE)


    H1:H3 now contains


    ON
    NATIONAL
    MICRO

  • Re: Partial VlookUp


    Quote from jonny

    Dear Will, you use FIND(" ",G1,1), but it not probably contains " ".


    So why did all three of the example you gave contain " " ?


    Quote


    in other table I have:
    ON SEMICONDUCTOR
    NATIONAL SEMICONDUCTOR
    MICRO SEMICONDUCTOR


    The solution will only ever be as good as the information provided.... ;)


    So why not give a proper example of the sample data that may exist... :)

  • Re: Partial VlookUp


    From your example spreadsheet it looks like you have a conversion from long string to short string in a seperate worksheet. So you can do a nested lookup. Virst do a lookup to find the the short string you are looking for and then use that result in your other lookup.


    If you resubmit your sample with two sheets, the one that has the conversions and the one with the data, then I can update it to include the proper formula.

  • Re: Partial VlookUp


    Sorry for inconvenience, my english is not so good to explain myself, so:


    I have two different tables with manufacturer names, some of manufacturer names are sam in two table and some are not.
    For manufacturer names that are differ I would like anyway to bring them.


    For examle: if in a first table I have name National and in a second table I have National Semiconductor, I want that VlookUp will bring National from the first table to the second table, cause "National Semiconductor" string contains "National" string.


    Or another example: 1-st table manufacturer Analog ,& 2-nd table manufacturer Analogitech. (Analogitech contains Analog, so VlookUp will find it).


    I hope now I clarified the issue.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!