Vlookup value after comma+space & return each value seperated by comma+space

  • I have a query which I am unable to solve.


    I have certain Codes in Sheet1 as below [TABLE="align: left, border: 1, cellpadding: 1, width: 600"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    CODES

    [/td]


    [td]

    FINAL OUTPUT

    [/td]


    [td]

    TOTAL

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    991121QR5, 991121XSX, 991234SSC, 991121SDF

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    991121QR5, 991121XSX, 991234SSC

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    991121QR5, 991121XSX, 991234SSC, 991121SDF

    [/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]









    Sheet2 is as follows- [TABLE="align: left, border: 1, cellpadding: 1, width: 400"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    CODES

    [/td]


    [td]

    VALUES

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    991121QR5

    [/td]


    [td]

    100100

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    991121XSX

    [/td]


    [td]

    888888

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    991234SSC

    [/td]


    [td]

    123456

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    991121SDF

    [/td]


    [td]

    121212

    [/td]


    [/tr]


    [/TABLE]










    The Final Outcome in Sheet 1 should be as follows:- [TABLE="align: left, border: 1, cellpadding: 1, width: 600"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    CODES

    [/td]


    [td]

    FINAL OUTPUT

    [/td]


    [td]

    TOTAL

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    991121QR5, 991121XSX, 991234SSC, 991121SDF

    [/td]


    [td]

    100100, 888888, 123456, 121212

    [/td]


    [td]

    1233656

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    991121QR5, 991121XSX, 991234SSC

    [/td]


    [td]

    100100, 888888, 123456

    [/td]


    [td]

    1112444

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    991121QR5, 991121XSX, 991234SSC, 991121SDF

    [/td]


    [td]

    100100, 888888, 123456, 121212

    [/td]


    [td]

    1233656

    [/td]


    [/tr]


    [/TABLE]









    How can I get the above OUTCOME using one single formula to vlookup each value from cell A2 after comma+space from Sheet1 with the table in Sheet2 and place the output in same format as in cell A2 of sheet 1 in cell B2 of Sheet 1. This should repeat till the last comma+space is there in the series. Also how to add up all the values of B2 in C2.

  • First add this VB function to your VB Editor (Alt+F11, Insert >> Module).



    Then back in the sheet, in B2 enter this Array* Formula:


    =SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(MATCH("*"&Sheet2!$A$2:$A$5&"*",Sheet1!A2,0)),Sheet2!$B$2:$B$5,"")," "))," ",", ")


    Adjust ranges to suit and copied down.


    In C2 enter this normal formula:


    =SUMPRODUCT(--(ISNUMBER(MATCH("*"&Sheet2!$A$2:$A$5&"*",A2,0))),Sheet2!$B$2:$B$5)


    adjust ranges to suit and copy down


    [arf]*[/arf]

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016


  • Thats Excellent but what if the code is present but it has no value or the code is not there in sheet2. Then in this case the sum should show an error. Can this be included as well.


  • I want the final outcome as below in case code is present but has no value or the code is not present at all. [TABLE="align: left, border: 1, cellpadding: 1, width: 600"]

    [tr]


    [td][/td]


    [td]

    A

    [/td]


    [td]

    B

    [/td]


    [td]

    C

    [/td]


    [/tr]


    [tr]


    [td]

    1

    [/td]


    [td]

    CODES

    [/td]


    [td]

    FINAL OUTPUT

    [/td]


    [td]

    TOTAL

    [/td]


    [/tr]


    [tr]


    [td]

    2

    [/td]


    [td]

    991121QR5, 991121XSX, 991234SSC, 991121SDF

    [/td]


    [td]

    [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

    [tr]


    [TD="width: 202"]100100, 888888, #NA, 121212

    [/tr][/td][tr]


    [/tr]


    [/TABLE]
    [/TD]

    [td]

    ERROR

    [/td]


    [/tr]


    [tr]


    [td]

    3

    [/td]


    [td]

    991121QR5, 991121XSX, 991234SSC

    [/td]


    [td]

    100100, 888888, #NA

    [/td]


    [td]

    ERROR

    [/td]


    [/tr]


    [tr]


    [td]

    4

    [/td]


    [td]

    991121QR5, 991121XSX, 991234SSC, 991121XYZ

    [/td]


    [td]

    100100, 888888, #NA, #NA

    [/td]


    [td]

    ERROR

    [/td]


    [/tr]


    [/TABLE]










    Please note I have added new code 991121XYZ which is not there in Sheet2's table. This has to throw an error.

  • Try this Array formula:


    =SUBSTITUTE(TRIM(aconcat(IF(ISNUMBER(SEARCH("*"&Sheet2!$A$2:$A$5&"*",Sheet1!A2)),Sheet2!$B$2:$B$5,"#N/A")," "))," ",", ")

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016

  • This query has been cross-posted on at least one other forum. Please provide links to all other instances of the same query.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Please add the link(s) I requested in post #8 before continuing - please do NOT ignore requests and instructions from moderators. Thanks.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Thank you. Please do not cross-post again without being honest at the start and providing links - it is disrespectful to all those trying to help you.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules