Index/Collect does not work with < symbol

Cesar Perez

Hi there,

I am using the INDEX COLLECT formulas to get a value if a number is between two ranges. The issue is that I'm getting an #INVALID VALUE error in the following formula:

=INDEX(COLLECT({TotalRiskFactor}, {RangeStart}, >=[Risk Score]@row, {RangeEnd}, <=[Risk Score]@row), 1)

The problem is with the "<" symbol in the second range.

Here are links to the test files. Any help with this issue will be appreciated.

Destination File:

https://app.smartsheet.com/b/publish?EQBCT=44a9480066c94f69899375439f3cf1f7

Source File:

https://app.smartsheet.com/b/publish?EQBCT=ca1acb5c634b4fb5b1a1ba8686638bb3

Paul Newcome

The issue is with your arguments overall. Using the Score of 40 (as in your examples)...

The score start range has to be 40 or greater, but the range end has to be less than or equal to 40.

Try reversing it so that the start is less than the score and the end is greater than the score.

Take a look at the second piece in your destination file. The formula is not throwing an error, but it is also not pulling the correct value in. It should be pulling in Moderate because 40 is between 35 and 54, but it is pulling in Moderate-High because that is the first entry where the Range Start is greater than 40.

Hollie Green

You have your greater than and less than backwards. Your formula is saying to looking for the start date to be greater than 40 and the 35 is less than 40.

Paul Newcome

The issue is with your arguments overall. Using the Score of 40 (as in your examples)...

The score start range has to be 40 or greater, but the range end has to be less than or equal to 40.

Try reversing it so that the start is less than the score and the end is greater than the score.

Take a look at the second piece in your destination file. The formula is not throwing an error, but it is also not pulling the correct value in. It should be pulling in Moderate because 40 is between 35 and 54, but it is pulling in Moderate-High because that is the first entry where the Range Start is greater than 40.

Hollie Green

You have your greater than and less than backwards. Your formula is saying to looking for the start date to be greater than 40 and the 35 is less than 40.

Cesar Perez

Thank you @Paul Newcome and @Hollie Green for the quick response. Both recommendations worked. I appreciate the help.

Paul Newcome

Happy to help. 👍️