Vlookup DragDown

Question

Im beginning a new job in a fabric. Actually, for each product, they have a excel file with the product operations in different sheets. I saw that a real problem, it takes a lot of time, and every time we should have to change the layout of the order, we are going to need to change a lot of excel files.. It will take forever and I think I can help on that.

So, my idea, is making a sheet with all the products and their operations and formulas:

Then, I have maked another sheet with the order fabrication with all the operations needed to make the product:

Every product will have an ID and im using in all cells the vlookup function from the ID:

=IFERROR(VLOOKUP($K$3;'estrutura-artigos2'!B8:V71;5;FALSE);"")

And Its Ok like you see on the last image, the problem is when I change the ID it doenst change like it should be (on my head):

Can anyone help me on this problem? I want it to change always to the ID I insert, and dont continue with the new ID.

Answer 1

=IFERROR(INDEX(Sheet1!E:E;MATCH(1;($K$3=Sheet1!$A:$A)*(COUNTIF($A$6:$A6;Sheet1!$E:$E)=0);0));"")

As your examples show no columns or rows I made my own. Please modify the formula to your ranges. Sheet1 is your datasheet. Column A in Sheet1 is the column holding the ID in your datasheet. Column E in Sheet1 is the column holding the 'op' in your datasheet (between qty and machine column). Counting starts at row 6 in my example, but modify it to the cell above where you put your formula.

Enter the formula with ctrl+shift+enter as it is an array formula.

Change the index for the different results, but keep the rest of the formula unchanged.

Updated answer based on the visibility in rows and columns: In A13 use =IFERROR(INDEX(datasheet!F$8:F$72;MATCH(1;($K$3=datasheet!$B$8:$B$72)*(COUNTIF($A$12:$A12;datasheet!$F$8:$F$72)=0);0));"") Match returns the row number of the first match where datasheet column B equals the value in $K$3 and where the values of datasheet column F don't equal any of the previous results of this formula in column A (above the current row number) The result is the row number that needs shown from the indexed column.

Answer 2

For the issue im having, this is as far I already went thanks to @P.b:

The problem im having is that the formula Im using goes right no operation 20, instead going to the operation 1, any idea how to fix this?

Formula using on the A13 = =IFERROR(INDEX(datasheet!F:F;MATCH(1;($K$3=datasheet!$B:$B)*(COUNTIF(datasheet!$B$8:$B71;datasheet!$F:$F)=0);0));"")