Forum Discussion
Question about matching multiple rows
Hi everyone, I'm working on an Excel dataset and looking for a proper function to generate a summarizing column. Here's the raw data format: I want to get a summary table like this: ...
Two solutions, similar to that of Twifoo , but based on the use of tables and named arrays.
The first treats the output list of products as an array and uses the named formula 'Supermarket?'
= SIGN( COUNTIFS( Table1[Category], Category, Table1[Supermarket], "yes" ) )
to determine the product availability from supermarkets. Similarly 'Grocery?'
= SIGN( COUNTIFS( Table1[Category], Category, Table1[Grocery], "yes" ) )
The columns of [0,1] are then combined within a named formula 'case' which refers to
= 1 + 2*Supermarket? + Grocery?
The final result is given by the worksheet formula
= LOOKUP( case, Suppliers )
where 'Suppliers' is the array
= {1,"Nowhere";2,"Grocery";3,"Supermarket";4,"Supermarket and Grocery"}