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: ...
The second solution is a variation that treats the category as a relative reference and so returns results category by category rather than as a single array. The formulas for availability from supermarket or grocery store are as before but the final lookup differs. This time 'Suppliers' is a 2x2 array
= {"Nowhere","Grocery";"Supermarket","Supermarket and Grocery"}
and INDEX can be used to return the appropriate text string
= INDEX( Suppliers, 1+Supermarket?, 1+Grocery? )
since the row and column numbers are now scalars.
No doubt both solution look somewhat strange but I include them to show that there is another way to develop spreadsheet solutions other than the traditional direct referencing and cloned single-cell formulas.