Trying to search 1 column of % to correspond with the column next to it. First % less than 100%

VL0001

I am trying to search this column for rows 11 through 19 starting top and working towards the bottom and once a cell has a % in it less than 100% i want it to reference the cell in the column next to it.

I have a formula that works if its 0% but not anything less than 100%.

Formula that works for 0% -

=INDEX([Task Name]11:[Task Name]19, MATCH(0, [% Complete]11:[% Complete]19, 0))

Formula that does not work for less than 100% -

=INDEX([Task Name]11:[Task Name]19, MATCH(true, [% Complete]11:[% Complete]19 < 100, 0)

VL0001

that gave me a invalid operations error. I ended up just creating an additional helper column that checks a box if the % is less than 100 and then the original column looks for the first checked box and then returns the text in the corresponding cell.

Adam Murphy

The MATCH function does not allow you to set the condition in the range section as you've done.

Try this: =INDEX([Task Name]11:[Task Name]19, MATCH(< 100, [% Complete]11:[% Complete]19, 0))

Hope this helps!

VL0001

that gave me a invalid operations error. I ended up just creating an additional helper column that checks a box if the % is less than 100 and then the original column looks for the first checked box and then returns the text in the corresponding cell.