IF formula not working

[Shayeebur]

Hi Chandoo,
There are columns with the name “Feedback Delivered Date” & my formula applies only on these columns & my formula is in column F that “First Feedback Date”. The formula is if column “Feedback Delivered Date” is contain NA then it jumps to the next “Feedback Delivered Date” & if date found then picks that date if not then searches for next. Please see cell F5The problem I am unable to nest more than 8 if formulas. Kindly suggest if you have better than this formula or the solution for this. I have attached a file also.

 

[Somendra Misra]

Hi Shayeebur,

You file contains hidden columns with formula ref. to column F and any shorter formula will take the range which will cover your hidden columns, so atlast it will be a circular ref. error. If you can remove those hidden columns. than you can use below array formula to get the result.

=INDEX($H3:$CT3,,SMALL(IF(ISNUMBER(IF($H$2:$CT$2="Feedback DeliveredDate",$H$3:$CT$3)),COLUMN($H$2:$CT$2)-COLUMN($H$2)+1),1))

Enter with Ctrl+Shift+Enter.

Regards,

 

[NARAYANK991]

Hi Shayeebur ,

Can you explain what the hidden columns are doing ?

I do not understand how you got Instance No. 4 for E3.

If those hidden columns are removed , then probably the Instance count can be derived through other formulae.

In that case , for F3 , you can try the following array formula , entered using CTRL SHIFT ENTER :

=MIN(IF($H$2:$BX$2="Feedback DeliveredDate",IF(ISNUMBER($H3:$BX3),$H3:$BX3)))

Narayan

 

[ashish mehra]

Hi Shayeebur,

To nest more than 8 IFs(.XLS) in a single cell use Helper Cell.
Kindly check the attached worksheet.

Regards,
AM