Forum Discussion

zkussad's avatar
zkussad
Copper Contributor
Jan 27, 2021

Complex IF Statement

Hello, I have an excel sheet I am setting up to use for my budget. I was wondering if there was a way to use IF functions to have the "Category" column determined by the description in column D...
Excel
  • mtarler's avatar
    mtarler
    Silver Contributor
    Jan 28, 2021

    PeterBartholomew1 I like your idea but think you should reverse it so the ENTRY is in the 'hidden' column and the formula is in the shown column (but should be locked) and then the formula would show the manual entry in the FALSE case.  In this way all the data is in the same column not just visually look like it is there:

    final look                                   showing actual entries

                 

    I also used an alternative on the formula so that it would find an ENTRY anywhere on the list (i.e. if the first entry was blank it can find a later entry to fill in the spot.  I think this could be important especially if the list was to get re-sorted based on other criteria or just to be able to sort based on Category.

     

    =IF([@[ Entry]]="",IFERROR(INDEX([ [ Entry] ],AGGREGATE(16,6,1/(([@Description]=[Description])*([ [ Entry] ]<>""))*(ROW([Description])-ROW(Table1[#Headers])),1)),""),[@[ Entry]])

     

     

Resources