Excel nested if, and and or statements testing for 1 or more items in a group, and comparing to 1 or more items in a second group to give a reply in one cell

Hi,

Thanks for illustrating the advantages of updating in a straightforward fashion when changes are needed, as inevitably they eventually will be. You make a compelling argument for vlookup and not using the IFS function. I imagine that a table would be easier to see and not miss any permutations, as you may miss by implementing IFS with its listing methdoogy mI will re-visit some tutorials about creating echanics. Over the next week or so I will revisit vlookup tables and how to create and implement them.

Well, Good night! 😴 I think you and I are on opposite sides of the world, guessing by when you appear active for my time zone.

Thanks for your help, as always!

Sincerely,

Daryl

Hi Daryl,

The advantage of using a table is to simplify the logic of your formulas so that each field will just have one generic formula i.e VLOOKUP this value in that table and whatever the calculation for that combination of values give me the cacluated result in column x in that table. Instead of embedding the logic of the table in each formula in each cell of the form. For example, lets say a week from the day you finished your form, your superior says oh we have a new product with new calculation, Daryl can you incorporate that new product calculation into the form you invented? You'll be scratching your head and start sweating profusely because now you have to figure out how to incorporate that new product into each formula in each cell and make it work. On the other hand if you used the table to simplify your logic having one generic formula on each cell looking for calculation variables, you can easily add that new product calculation in the table and since the table is defined as a table in excel it is dynamic to changes in the range to your generic VLOOKUP range in the cells of your form, and all you have to do is add the product calculation to the table. The maintainability of such a solution makes your job "easy" plus you get to impress your superior that you did it in such a short amount of time "kissing points from your superior" haha.

cheers

Wow- it’s a plan. I used to do v look up calls every month for reports that were required and became fluent in it. That was about 12 years ago, so I will have to re-learn that method. Once the table is made though, it sounds like a good solution.

If you are familiar with the newer IFS function that’s part of Office 365, do you have any thoughts on using it as a simple solution? Just wondering….

Cheers!

Daryl

Good morning,

Last night I uploaded to OneDrive and provided a link in my reply to Ossie.. Providing you can look at the workbook and Word narrative, I have written out the formula for each of the calculation methods.

Cheers!

Hi Daryl,

So for simplicity's sake, there are three selections that need to be paired with five selections, so 5x3=15.

so there are 15 combinations of selection between the DD1 and DD3 (DD being the Drop Down)

So create a table of those 15 pairings and show which of the 9 calculations belong to any of the 15 pairings so we can create a look up table that will always have already calculated the values of the 15 pairings, and our formula will just involve doing a VLOOKUP to specific combinations for example

CMS Cit. and LFV

which calculation should be used, we can have the cell already calculated and as soon as that selection has been made, in the look up table the two selections are concatenated as in: CMS Cit, LFV in one cell and the calculation in the adjacent cell so we can just have the calculation in the IF statement say

VLOOKUP(TEXTJOIN(",",FALSE,[DropDown1],[DropDown2]), [LookUpTableRange],[Column2],[FALSE]))

And the Calculation on the 2nd column of the lookup table will be returned in the Answer Cell in the Recap Surplus Hrdshp Stlmnt tab. So no mor if then else and or confusion but a simple vlookup

cheers