Results 1 to 10 of 10

Thread: VLOOKUP between 2 sheets in increments

  1. #1

    Exclamation VLOOKUP between 2 sheets in increments



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Good day I have 2 different sheets. I want to pull answers out of one data sheet into another. This is not a normal vlookup, because I have a sheet that calculates averages and minimums and maximums for 20 values of a certain intake code. Now this sheet has 20 values and all of them have the same intake code from row 2 to 21 . in another column I have calculated the answers I need. in row 19,20 and 21. Now I need to pull these answers according to that intake code into another sheet which has the intake code but not yet the average minimum and maximum nor the data needed to calculate those. I need the answers to pull into another sheet to give me those answers from sheet 1 in sheet 2 according to the particular intake code. Again, this is not a normal vlookup because I only want one rows answers to be displayed in sheet 2 from the 20 intake codes in sheet 1. I will upload the sheets if im not understood in this explanationThanks in advance ... Arnoux

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Yes, please do upload a sample workbook.


  3. #3

    Cool

    2015 Incoming QA F.zipIncoming TSS F.zip So it should pull from the "Inc TSS" sheet, to "the Inc QA" sheet. So I need the data to pull from p19 in Inc TSS sheet to column N in the Inc QA sheet according to the column "Intake codes". once I know how to pull one of the I will figure out to do the rest. Thank you for your help NVBC

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Sorry, I wasn't available for most of the day yesterday...

    Are you looking for the sum of column P for each code? Or are you looking for the 1st, last, .... not sure what exactly you mean by:
    I only want one rows answers to be displayed in sheet 2 from the 20 intake codes in sheet 1.


  5. #5
    Quote Originally Posted by NBVC View Post
    Sorry, I wasn't available for most of the day yesterday...

    Are you looking for the sum of column P for each code? Or are you looking for the 1st, last, .... not sure what exactly you mean by:
    Hi Thank you for helping me so quickly each time. In the sheet INC TSS... The value p19 gives is the MINIMUM PRESSURE of L2:L21 .... p39 gives the MINIMUM PRESSURE of L22:L41 etc..... You will see in column A lists 1-20 ... all of one specific "intake code", and then again 1-20 for another intake code. The answer to the =Min formula of the 20(column A 1-20) pressure values is in p19...... the max in p20 and the average in p21........ Now these answers should be pulled to the other sheet (INC QA sheet) into their respective columns (column N--> Min pressure) according to the intake code ..... So in short .... At The moment I must enter the answers manually into column N of the INC QA sheet, from the calculated answers found in p19 of the INC TSS sheet which takes forever. So I want the values to be brought over to the incoming QA sheet automatically after entering the intake code. ... if that makes sense and is even possible.

  6. #6
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Ok. I think I understand now.

    First, I recommend adding a helper column to Incoming TSS F.xlsx workbook in order to avoid array formulas.

    Add this formula to cell S2:

    =F2&H2&G2&R2

    and copy it down the rows.

    Now back in the QA workbook, column N of the first sheet... enter formula in N2:

    =IFERROR(INDEX('[Incoming TSS F.xlsx]Incoming TSS 2015'!$O:$O,MATCH($J3&LEFT(N$1,3),'[Incoming TSS F.xlsx]Incoming TSS 2015'!$S:$S,0)),"")

    copied across to P2, then down.

    The first portion are blank because there are no intake code matches as far as I can tell.

    P.S. Since you are numbering the rows in column A of the Incoming TSS F.xlsx workbook, you can simplify the formula a bit by removing the MOD() functions... try in O2:

    =IF($A2=18,MIN(OFFSET(K2,-17,,20,1)),IF($A2=19,MAX(OFFSET(K2,-18,,20,1)),IF($A2=20,AVERAGE(OFFSET(L2,-19,,20,1)),"")))

    copied down.

    Adjust column P and Q similarly.
    Last edited by NBVC; 2015-02-11 at 03:11 PM.


  7. #7
    That is absolutely amazing !. It works perfectly ! Just one more question. Is it possible to do the data pulling to column Q and R in the INC QA sheet as well. Q should pull from P21 and R should pull from Q21 from the INC TSS sheet......... so that eventually o19 to column N ,o20 to column O, o21 to column P, and p21 to column Q , and q21 to column R
    Last edited by Arnoux; 2015-02-12 at 08:06 AM.

  8. #8
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Sure, minor adustment to the INDEXed range is all that is needed.. so in Q2:

    =IFERROR(INDEX('[Incoming TSS F.xlsx]Incoming TSS 2015'!P:P,MATCH($J3&LEFT(Q$1,3),'[Incoming TSS F.xlsx]Incoming TSS 2015'!$S:$S,0)),"")

    copied to R2 and then down the sheet.


  9. #9
    Quote Originally Posted by NBVC View Post
    Sure, minor adustment to the INDEXed range is all that is needed.. so in Q2:

    =IFERROR(INDEX('[Incoming TSS F.xlsx]Incoming TSS 2015'!P:P,MATCH($J3&LEFT(Q$1,3),'[Incoming TSS F.xlsx]Incoming TSS 2015'!$S:$S,0)),"")

    copied to R2 and then down the sheet.
    You are a genius. Thanks so so much ! Whish I had your excel knowledge. Tell me does both sheets have to be open for the data to refresh and pull through.

  10. #10
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,508
    Articles
    0
    Excel Version
    Excel 2016
    Yes. It should work with the source book closed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •