Here is a simple matrix like data layout which shows the features available in every product. Let’s assume that this data is in range A2:E8 (including the header row)
| Functions | Product A | Product B | Product C | Product D |
| Function 1 | x | |||
| Function 2 | x | x | ||
| Function 3 | x | x | x | |
| Function 4 | x | x | ||
| Function 5 | x | x | ||
| Function 6 | x |
Here is a user created input table of his/her requirements. Let’s assume that this data is in range A12:B18 (including the header row)
| Functions | Input |
| Function 1 | |
| Function 2 | x |
| Function 3 | x |
| Function 4 | |
| Function 5 | x |
| Function 6 |
The expected result is the “Product” which meets the user defined function combinations. The result should be Product B. If there are different products which meet the user’s requirements, only the first one will be returned as a result. If one would like all products to be returned, the one can use Power Query to resolve this problem.
=IFERROR(INDEX($B$2:$E$2,1,MATCH(COUNTA($A$13:$A$18),MMULT(1*(TRANSPOSE(B3:E8=B13:B18)),1*(ISNUMBER(ROW(INDIRECT(“1:”&COUNTA($A$3:$A$8)))))),0)),”No such product”)
Please note that this is an array formula so please confirm the formula with Ctrl+Shift+Enter.
Return the specific product which satisfies the user defined feature combination
{ 2 Comments }