I have this formula, and I paste in cells, to give the concatenation of two cells values based on some conditions, it worked fine, until i had to increase the IF functions, and saw that there’s a limit.
I do not know how to use Vlookup or the WATCH function. Never used any of this.
I just want this formula to work when I paste it in the cells. I paste it in about more than 200 cells. and it’s pretty fast.
I’d appreciate any direct help, so the formula works, I do not know much of excel or vba.
=IF(AND(AQ1<=-6),"-6",
IF(AND(AQ1=-5,AR1>=5),CONCATENATE(AQ1,".5"),
IF(AND(AQ1=-4,AR1<4,AR1>=2),CONCATENATE(AQ1,".2"),
IF(AND(AQ1=-4,AR1<7,AR1>=4),CONCATENATE(AQ1,".4"),
IF(AND(AQ1=-4,AR1>=7),CONCATENATE(AQ1,".7"),
IF(AND(AQ1=-3,AR1<2,AR1>=0),CONCATENATE(AQ1,".0"),
IF(AND(AQ1=-3,AR1<3,AR1>=2),CONCATENATE(AQ1,".2"),
IF(AND(AQ1=-3,AR1<4,AR1>=3),CONCATENATE(AQ1,".3"),
IF(AND(AQ1=-3,AR1<5,AR1>=4),CONCATENATE(AQ1,".4"),
IF(AND(AQ1=-3,AR1<6,AR1>=5),CONCATENATE(AQ1,".5"),
IF(AND(AQ1=-3,AR1<8,AR1>=6),CONCATENATE(AQ1,".6"),
IF(AND(AQ1=-3,AR1>=8),CONCATENATE(AQ1,".8"),
IF(AND(AQ1=-2,AR1<1,AR1>=-1),CONCATENATE(AQ1,".-1"),
IF(AND(AQ1=-2,AR1<2,AR1>=1),CONCATENATE(AQ1,".1"),
IF(AND(AQ1=-2,AR1<3,AR1>=2),CONCATENATE(AQ1,".2"),
IF(AND(AQ1=-2,AR1<4,AR1>=3),CONCATENATE(AQ1,".3"),
IF(AND(AQ1=-2,AR1<5,AR1>=4),CONCATENATE(AQ1,".4"),
IF(AND(AQ1=-2,AR1<6,AR1>=-5),CONCATENATE(AQ1,".5"),
IF(AND(AQ1=-2,AR1<7,AR1>=6),CONCATENATE(AQ1,".6"),
IF(AND(AQ1=-2,AR1<9,AR1>=7),CONCATENATE(AQ1,".7"),
IF(AND(AQ1=-2,AR1>=9),CONCATENATE(AQ1,".9"),
IF(AND(AQ1=-1,AR1<-1,AR1>=-2),CONCATENATE(AQ1,".-2"),
IF(AND(AQ1=-1,AR1<0,AR1>=-1),CONCATENATE(AQ1,".-1"),
IF(AND(AQ1=-1,AR1<1,AR1>=0),CONCATENATE(AQ1,".0"),
IF(AND(AQ1=-1,AR1<2,AR1>=1),CONCATENATE(AQ1,".1"),
IF(AND(AQ1=-1,AR1<3,AR1>=2),CONCATENATE(AQ1,".2"),
IF(AND(AQ1=-1,AR1<4,AR1>=3),CONCATENATE(AQ1,".3"),
IF(AND(AQ1=-1,AR1<5,AR1>=4),CONCATENATE(AQ1,".4"),
IF(AND(AQ1=-1,AR1<6,AR1>=5),CONCATENATE(AQ1,".5"),
IF(AND(AQ1=-1,AR1<7,AR1>=6),CONCATENATE(AQ1,".6"),
IF(AND(AQ1=-1,AR1<9,AR1>=7),CONCATENATE(AQ1,".7"),
IF(AND(AQ1=-1,AR1>=9),CONCATENATE(AQ1,".9"),
IF(AND(AQ1=0,AR1<-1,AR1>=-3),CONCATENATE(AQ1,".-3"),
IF(AND(AQ1=0,AR1<0,AR1>=-1),CONCATENATE(AQ1,".-1"),
IF(AND(AQ1=0,AR1<1,AR1>=0),CONCATENATE(AQ1,".0"),
IF(AND(AQ1=0,AR1<2,AR1>=1),CONCATENATE(AQ1,".1"),
IF(AND(AQ1=0,AR1<3,AR1>=2),CONCATENATE(AQ1,".2"),
IF(AND(AQ1=0,AR1<4,AR1>=3),CONCATENATE(AQ1,".3"),
IF(AND(AQ1=0,AR1<5,AR1>=4),CONCATENATE(AQ1,".4"),
IF(AND(AQ1=0,AR1<6,AR1>=5),CONCATENATE(AQ1,".5"),
IF(AND(AQ1=0,AR1<7,AR1>=6),CONCATENATE(AQ1,".6"),
IF(AND(AQ1=0,AR1<8,AR1>=7),CONCATENATE(AQ1,".7"),
IF(AND(AQ1=0,AR1>=8),CONCATENATE(AQ1,".8"),
IF(AND(AQ1=1,AR1<-2,AR1>=-3),CONCATENATE(AQ1,".-3"),
IF(AND(AQ1=1,AR1<-1,AR1>=-2),CONCATENATE(AQ1,".-2"),
IF(AND(AQ1=1,AR1<0,AR1>=-1),CONCATENATE(AQ1,".-1"),
IF(AND(AQ1=1,AR1<1,AR1>=0),CONCATENATE(AQ1,".0"),
IF(AND(AQ1=1,AR1<2,AR1>=1),CONCATENATE(AQ1,".1"),
IF(AND(AQ1=1,AR1<3,AR1>=2),CONCATENATE(AQ1,".2"),
IF(AND(AQ1=1,AR1<4,AR1>=3),CONCATENATE(AQ1,".3"),
IF(AND(AQ1=1,AR1<5,AR1>=4),CONCATENATE(AQ1,".4"),
IF(AND(AQ1=1,AR1<6,AR1>=5),CONCATENATE(AQ1,".5"),
IF(AND(AQ1=1,AR1<8,AR1>=6),CONCATENATE(AQ1,".6"),
IF(AND(AQ1=1,AR1>=8),CONCATENATE(AQ1,".8"),
IF(AND(AQ1=2,AR1<0,AR1>=-2),CONCATENATE(AQ1,".-3"),
IF(AND(AQ1=2,AR1<0,AR1>=-1),CONCATENATE(AQ1,".-2"),
IF(AND(AQ1=2,AR1<1,AR1>=0),CONCATENATE(AQ1,".-1"),
IF(AND(AQ1=2,AR1<2,AR1>=1),CONCATENATE(AQ1,".0"),
IF(AND(AQ1=2,AR1<3,AR1>=2),CONCATENATE(AQ1,".1"),
IF(AND(AQ1=2,AR1<4,AR1>=3),CONCATENATE(AQ1,".2"),
IF(AND(AQ1=2,AR1<5,AR1>=4),CONCATENATE(AQ1,".3"),
IF(AND(AQ1=2,AR1<6,AR1>=6),CONCATENATE(AQ1,".4"),
IF(AND(AQ1=2,AR1>=6),CONCATENATE(AQ1,".6"),
IF(AND(AQ1=3,AR1<2,AR1>=-1),CONCATENATE(AQ1,".-3"),
IF(AND(AQ1=3,AR1<3,AR1>=0),CONCATENATE(AQ1,".-1"),
IF(AND(AQ1=3,AR1<3,AR1>=1),CONCATENATE(AQ1,".0"),
IF(AND(AQ1=3,AR1<4,AR1>=2),CONCATENATE(AQ1,".1"),
IF(AND(AQ1=3,AR1<5,AR1>=3),CONCATENATE(AQ1,".2"),
IF(AND(AQ1=3,AR1<6,AR1>=5),CONCATENATE(AQ1,".3"),
IF(AND(AQ1=3,AR1>=5),CONCATENATE(AQ1,".5"),
IF(AND(AQ1=4,AR1<5,AR1>=0),CONCATENATE(AQ1,".-4"),
IF(AND(AQ1=4,AR1<6,AR1>=2),CONCATENATE(AQ1,".0"),
IF(AND(AQ1=4,AR1>=2),CONCATENATE(AQ1,".2"),
IF(AND(AQ1=5,AR1>=0),CONCATENATE(AQ1,".0"),
IF(AND(AQ1>=6),"6",
AQ1)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))
From this Image. You’ll see that I give values, and not the sheet giving me values, The values that i give to be imputed in sheet AO are most times NOT dependent on AR, but the Values are always dependent on AQ. and from the results of the IF conditions, the values that have been pre configured by me are entered into AO. This is the Reason why I think that the MATCH and INDEX functions will not be useful in this formula
***
6
You can set up a table with every combination of inputs, such as:
and use a formula to check if the conditions are met, for example something like:
=F2+
INDEX(D2:D3,
MATCH(1,
(F2=A2:A3) *
(G2<B2:B3) *
(H2>=C2:C3),
0)
)
This formula adds a value from cell F2 to a value returned by the INDEX function. It uses multiple criteria to determine which value to retrieve from a range (D2:D3) using the MATCH function. Here’s a breakdown of each component:
-
F2 +- The formula starts with the value in
F2and adds the result of theINDEXfunction. You can useCONCATENATEinstead.
- The formula starts with the value in
-
INDEX(D2:D3, ...)- The
INDEXfunction retrieves a value from the rangeD2:D3. The row number to retrieve is determined by theMATCHfunction.
- The
-
MATCH(1, ..., 0)- The
MATCHfunction finds the position of the first occurrence of1in the array produced by the logical criteria. The0at the end specifies an exact match.
- The
-
Logical Criteria:
(F2=A2:A3)- Checks if the value in
F2matches any value in the rangeA2:A3. This results in an array ofTRUEorFALSEvalues.
- Checks if the value in
(G2<B2:B3)- Checks if the value in
G2is less than corresponding values inB2:B3. This results in an array ofTRUEorFALSEvalues.
- Checks if the value in
(H2>=C2:C3)- Checks if the value in
H2is greater than or equal to the corresponding values inC2:C3. This also produces an array ofTRUEorFALSEvalues.
- Checks if the value in
-
(F2=A2:A3) * (G2<B2:B3) * (H2>=C2:C3)- These conditions are multiplied together. In Excel,
TRUEis treated as1andFALSEas0. Therefore:- If all conditions are
TRUEfor a specific row, the product is1. - Otherwise, the product is
0.
- If all conditions are
- These conditions are multiplied together. In Excel,
-
MATCH(1, ..., 0)- The
MATCHfunction looks for the first1in the resulting array (where all conditions areTRUE) and returns its position. This position is used as the row number for theINDEXfunction.
- The
How It Works:
- The formula looks for the first row in the ranges where:
- The value in
A2:A3equalsF2. - The value in
G2is less than the value inB2:B3. - The value in
H2is greater than or equal to the value inC2:C3.
- The value in
- Once a matching row is found, the corresponding value from
D2:D3is retrieved. - This value is then added to the value in
F2.
5