If Then Shortcut

Copper Contributor

Hi, I'm trying have a cell look about 100 cells on a particular row (with formulas) for the first cell that has a number greater than 0 (zero) and then look and then look at another row of 100 cells for a corresponding date. Is there a way to write a shortcut formula without having to write the normal =if(a1>0,c1,If(a2>0,c2 etc etc etc...))?

 

I tried to do this 

=IF(B50:CM50>0,B41:CM41)

but it did not work and it came back with a #SPILL! error.

 

Thanks 

2 Replies

@GreggBfree 

=INDEX($A$2:$V$2,SMALL(IF(A3:V3>0,COLUMN(A:V)),1))

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.

index and small.JPG 

@GreggBfree The spill error indicates the formula is returning an array of multiple values and it's trying to place each value in a new column.  Somewhere to the right of your formula, there is data or text in a cell that is within the range of the returned array.  

 

You can highlight the formula in the formula bar and press F9 in order to see what it's returning.  Then either escape or undo so that the formula remains instead of the static value it returned.  You will likely see it is returning a date for every instance instead of only the first.  

 

Try this for the first instance:  

=INDEX(B41:CM41,1,MIN(IF(B50:CM50<0,COLUMN(B50:CM50)))-COLUMN(B50)+1)