Need help to Loop a statement to shorten code

[JessieRitgen]

I am looking to do the following:

If A2 in table1 matches A2 in table2, and if number in B2 from table1 is less than B2 in table2, then say "high", If A2 in table1 matches A2 in table2, and the number in B2 from table1 is greater than B2 in table2 and less than C2 in table2, then give cell value of 3. If the number in B2 from table1 then is larger than C2, then add another 3 so the cell says 6. Keep doing this until the first statement of A2 in table1 and A2 in table2 is not true, and when not true, begin the cycle over - so If A2 in table1 matches A2 in table2, and if number in B2 from table1 is greater than B2 in table2 and less than C2 in table2, then give cell value of 3. If the number in B2 from table1 then is large than C2, then add another 3 so the cell says 6, etc.

My original code basically wrote the same code to cover the number of columns I had (78 in total). It worked but this was too much and crashed excel. It was copy and paste of code, changing column look-ups and then giving the cell the assigned value (ie "3"). I'm looking to do the above but with a shorter code string. Ideas? I feel I need it to add 3 based on if a statement is true (or not). I can start the statement, but I can't figure out the increment part to have it change to 6, then 9, etc and then start back at 3 when the A2 cells aren't matching anymore.

Attached a spreadsheet with data and layout i'm using.


The original, very long statement i used: (could get the job done, but couldn't compute past the first 5 rows without buggering up)

=IF(((VLOOKUP(A2,'3mTOPS'!A$2:BD$434,1,FALSE))='LAS data'!A2)*AND('LAS data'!B2<(VLOOKUP(A2,'3mTOPS'!A$2:BD$434,2,FALSE))),"high",IF(((VLOOKUP(A2,'3mTOPS'!A$2:BD$434,1,FALSE))='LAS data'!A2)*AND('LAS data'!B2>=(VLOOKUP(A2,'3mTOPS'!A$2:BD$434,2,FALSE)))*AND('LAS data'!B2<(VLOOKUP(A2,'3mTOPS'!A$2:N$434,3,FALSE))),"3",IF(((VLOOKUP(A2,'3mTOPS'!A$2:BD$434,1,FALSE))='LAS data'!A2)*AND((VLOOKUP(A2,'3mTOPS'!A$2:BD$434,3,FALSE))=""),"3",IF(((VLOOKUP(A2,'3mTOPS'!A$2:BD$434,1,FALSE))='LAS data'!A2)*AND('LAS data'!B2>=(VLOOKUP(A2,'3mTOPS'!A$2:BD$434,3,FALSE)))*AND('LAS data'!B2<(VLOOKUP(A2,'3mTOPS'!A$2:N$434,4,FALSE))),"6",IF(((VLOOKUP(A2,'3mTOPS'!A$2:BD$434,1,FALSE))='LAS data'!A2)*AND((VLOOKUP(A2,'3mTOPS'!A$2:BD$434,4,FALSE))=""),"6",IF(((VLOOKUP(A2,'3mTOPS'!A$2:BD$434,1,FALSE))='LAS data'!A2)*AND('LAS data'!B2>=(VLOOKUP(A2,'3mTOPS'!A$2:BD$434,4,FALSE)))*AND('LAS data'!B2<(VLOOKUP(A2,'3mTOPS'!A$2:N$434,5,FALSE))),"9",IF(((VLOOKUP(A2,'3mTOPS'!A$2:BD$434,1,FALSE))='LAS data'!A2)*AND((VLOOKUP(A2,'3mTOPS'!A$2:BD$434,5,FALSE))=""),"9",...... etc until i got to ,"78","blank")))))))))))))))))))))))))))))))))))))))))))))))))))))

[Dante Amor]

Hi and welcome to Excel Forum.

Try this array formula

=SUMAPRODUCTO((Table2!$A$2:$A$400=A2)*(K.ESIMO.MENOR(SI(Table2!$B$2:$CA$400>B2,Table2!$B$1:$CA$1),1)))

Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself.

[JessieRitgen]

thank you! the sequence you provided worked for that example, but my full worksheet has a difference:

The number in A2 isn't always matching the A2 position in table2. I used the lookup part to match the A2 values to each other, then it would do the rest. When I put my true data set in the formula reads 0 all the way down. Does your formula account for the fact it needs to search table2 for it's match in Column A?

[Dante Amor]

Does your formula account for the fact it needs to search table2 for it's match in Column A?

Of course.

Table2!$A$2:$A$400=A2, in this part look for A2 in column A of "Table2" sheet, from cell A2 to A400, you can change to A1000 or until the last row with data.

It is an array formula to enter you must press Shift + Control + Enter, not just Enter.

Check out the example I put in the file I attached.

[JessieRitgen]

I ended up seeing that afterward (thanks!) (i must write my formulas the long, old way compared to how you write)

When I use the entire data set I have, it means the values in column A aren't in the same sequence in both tables. The number that shows up first in column A, table1 isn't the first that shows up in column A table2, and so on. But this shoiuldn't matter because you said it 'looks up' for the match in table2, right? When I expanded the formula to the rest of the data set, I got zeros for everything. I put in the total row number (432 instead of 400 for Table2) and expanded to the column it completed (BE instead of CA for Table2) but still got zeros.

Also, in the sheet you sent, when a different number in A column table1 was encountered, it started at zero. It needs to start at 3 and sequence from there. I wasn't sure what to change to account for that.

Because I"m hoping to learn more about the logic behind the formula, could you break it down and explain the functions? I looked up SUMPRODUCT, etc, but I wasn't sure why you used it. Same with 'SMALL'.

[Dante Amor]

Sorry, I forgot to translate the formula.
Here is the formula with the new dimensions:

{=SUMPRODUCT((Table2!$A$2:$A$432=A2)*(SMALL(IF(Table2!$B$2:$BE$432>B2,Table2!$B$1:$BE$1),1)))}


I attach the file again.
The first result for Dept. 429 in cell C2 is 3.
This is how it goes in the file I sent you and you can check it in this new file:

[bebo021999]

Alternative non array solution:

C2:

=INDEX(Table2!$1:$1,AGGREGATE(15,6,COLUMN(Table2!$B$1:$BZ$1)/(Table2!$A$2:$A$1000=$A2)/(Table2!$B$2:$BZ$1000>=$B2+10^-3),1))

Drag down