0

Based on two input conditions, I wish to be able to output 1 of 4 output values. For example when checking for matches between two tables I would like to be able to access a function like:

=TRUTHTABLE(condition1,condition2,true-true,true-false,false-true,false-false)

to do something like:

=TRUTHTABLE(MATCH(value,array1,0),MATCH(value,array2,0),"Good","Missing","Redundant","Good")

The two options I've come up with so far are:

=IF(condition1,IF(condition2,true-true,true-false),IF(condition2,false-true,false-false))

or:

=CHOOSE(1--condition1*2--condition2,false-false,false-true,true-false,true-true)

Option 1 is cumbersome to maintain due to the repetition of condition2, and option 2 is convoluted to understand for future maintainers. I'm currently tending toward option 2, but does anyone know any better option?

Machavity
  • 30,841
  • 27
  • 92
  • 100
Mark
  • 73
  • 7
  • you are only dealing with 4 options so its relatively small to manage. you could writ out the table in 4 rows and 3 columns, Column A possibile results of condition1 match with possible results of condition 2 in column B. Each pair/row forms a unique identifier. Then you just look at your table for the row where condition 1 and condition 2 are true and return the result from column C in the matching row. – Forward Ed May 31 '16 at 10:38
  • If I was using the formula just once or twice that could work, but I plan to have this repeated in every row of a column several hundred cells high. – Mark May 31 '16 at 10:41
  • [A potentially easier way to manage multiple if results](http://stackoverflow.com/questions/37459915/check-for-combinations-in-excel/37463146#37463146) instead of going into a nest IF table. As long as your four possible results remain the same, you should be able to copy the formula down beside your column of data. – Forward Ed May 31 '16 at 10:52
  • Thanks. I'll have a go at that approach this afternoon. – Mark May 31 '16 at 11:04
  • The part of your question that reminded me of this was future maintainability. Yes the formula is potentially crazy, but your table that you are going to check against is relatively easy to maintain. – Forward Ed May 31 '16 at 11:08
  • Thanks for this, which I will keep in mind for future more complex occasions. For my current 2 x true/false the CHOOSE option seems best. – Mark Jun 08 '16 at 08:51

1 Answers1

0

With inputs of A1 and B1:

=LOWER(A1&"-"&B1)

A1 for example might contain:

=NOT(ISERROR(MATCH(value,array1,0)))
pnuts
  • 58,317
  • 11
  • 87
  • 139