Dynamic IF/VLookup Formula Help

[ckp90c]

Hello All,

I have an existing IF Statement (shown below) in Col P of the "Book Average Balance" sheet which has many conditions that will pull (sign reversed) data from Col K if all are met. That old formula does work, however it is not dynamic to update. If there are new accounts that are added to the criteria, a user will have to add that to the formula and then copy down, which will be cumbersome.

I want to instead leverage a second sheet called "Lookup" (shown below) that contains a few manageable columns that a user can add/remove items from. Then instead of that long, cumbersome IF statement, I wanted to do a new formula that may be a combination of an IF statement and VLookups that can automatically refer to those columns and give the same result.

Can anyone help me on this? I am so stuck and haven't been able to find the right answer here. Thank you!!!

 

[arthurbr]

Make each of your columns in the Lookup tab a different Excel Table and replace your " OR(B5="....)" condition with ISNUMBER(MATCH(B5,account_table-ref,0))
where "account_table-ref" is the name of the table you created with account numbers.
Do the same for the other conditions
Be careful that your "numbers" in the lookup tables are text and not numbers as your picture seems to indicate

 

[ckp90c]

Hey I did some initial testing and it looks like it works really well. Thank you so much!