VLookup

    Good Day

    I use Vlookup quite extensively to cross reference documents

    I was wondering if there is a way to cross reference 2 different fields / values in 1 row. For instance:
    Sheet A contains information:


    Ons sheet B, row 10 and 11 refers to two values each on Sheet A. I need both values from Colum B Sheet 1 to reflect in rows 10 and 11, Colum C sheet 2



    Reversely, Sheet A colum C and D refers back to sheet 2. But as the relevant rows in Sheet 2 contain 2 values, they are not populates


    Example sheet attached

    Please assist?


    thank you

    Thank you

    Unfortunately, I need to have 2 values populated in Cell C10 and C11 each, which are reffered to By A10 and A11 on sheet B

    So, if it works, it needs to look like this:

    5,6 120 Receiption, Cleaner
    7,8 125 Gardener, Maintenance Manager


    info Automated from Sheet 1:


    Job Numbers
    Job Number Job Title
    1 Director
    2 Marketing
    3 Financial
    4 Sales
    5 Reception
    6 Cleaner
    7 Gardener
    8 Maintenance Manager


    Any help would be appreciated

    Quote

    Unfortunately, I need to have 2 values populated in Cell C10 and C11 each, which are reffered to By A10 and A11 on sheet B

    So, if it works, it needs to look like this:

    5,6120Receiption, Cleaner
    7,8125Gardener, Maintenance Manager


    in C10 sheet B:

    =VLOOKUP(--LEFT(A10),A!$A$6:$D$13,2,0)&", "&VLOOKUP(--RIGHT(A10),A!$A$6:$D$13,2,0)

    and drag to C11

    1] In Sheet "A" C6, formula copied right to D6 and all copied down :


    =INDEX(B!F:F,LOOKUP(1,0/FIND($A6,B!$A$6:$A$11),ROW(B!$A$6:$A$11)))



    2] In Sheet "B" C6, formula copied down :


    =TEXTJOIN(", ",,IFERROR(LOOKUP(0+MID(A6,{1,3},1),A!$A:$B),""))


    Thank you bosco_yip


    Referring to Sheet B, as this is the first one I need to do

    In total we have 65 positions, and some employees can have up to 5 positions / responsibilities


    a. The formula supplied only works to a max of 2 position per employee, and up to 9 positions. Is it possible to update it to accommodate the additional parameters?

Participate now!

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

Register Yourself Login