# Another way to do equiv. of embedded =if(...) since no room

#### shmizzy

##### New Member
I have 20 columns of data. On ea. row I am checking from right to left for 1st value that is negative. So I built this:

=(IF((Z10<A10),(Z10),((IF((Y10<A10),(Y10),((IF((X10<A10),(X10),((IF((W10<A10),(W10),((IF((V10<A10),(V10),((IF((U10<A10),(U10),((IF((T10<A10),(T10),((IF((S10<A10),(S10),(0))))))))))))))))))))))))

where a10 holds 0. I want to keep building this to check through col d. but when i build it more where the 0 is I get error. I assuming there is limit on lenght of expression. Looking for a way around this to find first negative # from (r) to (l)?

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### acw

##### MrExcel MVP
Hi

Formula didn't show properly, so I've assumed your 20 columns are G:Z

Try

=INDEX(A40:Z40,MAX(IF(G40:Z40<0,COLUMN(G40:Z40),0)))

Tony

##### MrExcel MVP
Re: Another way to do equiv. of embedded =if(...) since no r

I have 20 columns of data. On ea. row I am checking from right to left for 1st value that is negative. So I built this:

=(IF((Z10 <A10),(Z10),((IF((Y10 <A10),(Y10),((IF((X10 <A10),(X10),((IF((W10 <A10),(W10),((IF((V10 <A10),(V10),((IF((U10 <A10),(U10),((IF((T10 <A10),(T10),((IF((S10 <A10),(S10),(0))))))))))))))))))))))))

where a10 holds 0. I want to keep building this to check through col d. but when i build it more where the 0 is I get error. I assuming there is limit on lenght of expression. Looking for a way around this to find first negative # from (r) to (l)?

=MATCH(TRUE,A10:Z10 < 0,0)

confirmed with control+shift+enter, will return if available the position of the first negative number in A10:Z10.

=INDEX(A10:Z10,MATCH(TRUE,A10:Z10 < 0,0))

confirmed with control+shift+enter, would return if available the first negative number in A10:Z10.

confirmed with control+shift+enter, would return if available the cell reference of the first negative number in A10:Z10.

Replies
6
Views
233
Replies
16
Views
459
Replies
5
Views
694
Replies
10
Views
418
Replies
1
Views
301

### Forum statistics

1,136,272
Messages
5,674,749
Members
419,525
Latest member
helensesc ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back