VLOOKUP between 2 sheets in increments

A

Arnoux

New member
Joined
Feb 2, 2015
Messages
39
Reaction score
0
Points
0
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
 
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.
Click to expand...
 
NBVC said:
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:
Click to expand...

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.
 
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:
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:
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.
 
NBVC said:
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.
Click to expand...
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.
 
Yes. It should work with the source book closed
 
You must log in or register to reply here.
Back
Top