The code is to look for a string in the first row and then perform some action with the nearby rows and columns according to the results of the boolean formulas. Eventually, I will change the "Ranges" in the formula with a defined input cell name.

This is the formula that is giving me the error:

{=AND( INDEX(TempTable, MATCH("Ranges", TempColArray, 0) +1, 0) = "—", OR( ISBLANK(INDEX(TempTable, MATCH("Ranges", TempColArray, 0) +1, 2)), BR16 <= INDEX(TempTable, MATCH("Ranges", TempColArray, 0) +1, 2) ) )}

At the moment, BR16 = 1000

I separated it into parts to see if each segment was working, and these were the results:

UPDATE: I noticed that the 1st and 2nd statements of the OR formula on the image are reversed.

To avoid misunderstandings, by "1st statement", I mean it's the first line/logical expression of the OR/AND formula, and the 2nd statement of the AND formula is the OR formula.

To emphasize the problem, the AND formula is returning FALSE when it should return TRUE.

TRUE OR FALSE = TRUE

TRUE AND TRUE = TRUE

TRUE AND (TRUE OR FALSE) = TRUE

However, for the last one, excel is returning FALSE.

This is the Table (TempTable) I created to test the formula (to the left of the green line is TempColArray):

2

2 Answers

This is an array formula. The INDEX formula will return an array of three booleans. The OR statement returns a single boolean. You can see this when you step through the formula with the Evaluate Formula tool. The combined value of all the boolean values is FALSE.

In order to return a TRUE, all values inside an AND() statement must be true. This is the reason why your formula returns FALSE. How to fix it cannot be answered without knowing more about the business logic and what you want to calculate.

1

I found the error.

In the INDEX function, I put 0 as the column when I wanted it to select the first column of the table.

For this, I should have put 1.

By putting 0, the INDEX function returned the value of all the cells in the selected row instead of just the value of the first row.

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy