Forum Discussion
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. I go to the same places often, so I was hoping to be able to just enter the name of the restaurant and have it auto-populate in Column E as "Restaurant". Also willing to try solutions outside of the IF function is there is a better way. Thanks in advance.
Create a separate list or table with unique descriptions in the first column and the the corresponding category in the second column. Let's say that this list is in K2:L50.
Clear column E in the table (from E2 down).
Enter the following formula in E2:
=IFERROR(VLOOKUP([@Description],$K$2:$L$50,2,FALSE),"")
Excel should automatically extend the formula to all rows of the table.
More 'smoke and mirrors'.
The missing categories are created by a formula in an almost hidden column.
I prefer XLOOKUP to the INDEX/MATCH but that requires Office 365.
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]])
