clustered if else combination

[saidineshbabu]

Hi,

I have a requirement for this complex nested loop. Would be great if I get a solution for this.

Column A and Column B has values ranging between 1 - 35 in both the columns. Based on these conditions I need the value to be printed in C.

Both A and B should be between the Range of 31-35 then the output should be 7
Any one of the column should be 31-35 and other column should be in 26-30 then the output should be 6
Both A and B should be between the Range of 26-30 then the output should be 5
Any one of theColumn should be between 26-30 and other column should be between 21-25 then the output should be 4
Any one of the column should be between 31-35 and other column is below 20 then the output should be 3
Any one of the column should be between 21-25 and other column is below 20 then the output should be 2
Both the columns are below 20. then the output should be 1

it will ideally have 35*35 combinations. but I have attached a same with limited combinations. Pls refer sample.xlsx

Thanks in advance

[Limor_OP]

Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.

[saidineshbabu]

Sure @belinda200
Attached the sample excel.

[Limor_OP]

Any one of the column should be 31-35 and other column should be in 26-30 then the output should be 6

according to that- why would 30 and 30 in line 43 for example result in 6 ?
are you sure about the values you inserted manually in your file?

[protonLeah]

You need a helper column for each test condition:
D2, a, b> 30

Please Login or Register  to view this content.

E2, 26>=a&b<=30

Please Login or Register  to view this content.

F2, a&b<20

Please Login or Register  to view this content.

G2, a|b>30, 20>=b|a<=30

Please Login or Register  to view this content.

Results in C2:

Please Login or Register  to view this content.

[saidineshbabu]

according to that- why would 30 and 30 in line 43 for example result in 6 ?
are you sure about the values you inserted manually in your file?

sorry my bad. Updated the excel sheet to reflect correctly.

[saidineshbabu]

Thanks protonLeah.

But the resutls are emply in few cells though the condition is met. Can you pls help?

[Bo_Ry]

Please set table as in F1:J5
then
C2
=VLOOKUP(A2,$F$2:$J$5,MATCH(B2,$F$1:$J$1))

[Limor_OP]

Hi,
Without helper this would be the formula in D2 and down:

Please Login or Register  to view this content.

[saidineshbabu]

Please set table as in F1:J5
then
C2
=VLOOKUP(A2,$F$2:$J$5,MATCH(B2,$F$1:$J$1))

This worked too. thanks!

[saidineshbabu]

Hi,
Without helper this would be the formula in D2 and down:

Please Login or Register  to view this content.

This worked too. Thanks!