Hi
Attached, I have an Excel 2013 sheet with a toy model of the problem in another much lagrer workbook.
In the table, I want to take the product of the numbers in the columns "1" and "2" for the row with ID "b". I use INDEX to search for the row with ID "b": INDEX( Table1; MATCH( "b", Table1[ID], 0 ), 0 ). This returns the whole row as a range; ("b", 50%, 50% ).
Then I thought I could use an array formula to make a range (0, 1, 1) from the column headers, like this:
( Table1[#Headers] = "1") + ( Table1[#Headers] = "2" )
Inside a formula that can take as input an array formula, like MATCH( 1; ( Table1[#Headers] = "1") + ( Table1[#Headers] = "2" ); 0), this input makes sense.
Now I would like to take the product of my two ranges ( "b", 150%, 150%) and ( 0, 1, 1 ) to get (0, 150%, 150%), and somehow take the product of the non-empty values.
It would be great if someone could fill in the neccessary steps. Or perhaps there is another more effective approach. Thanks in advance!
Bookmarks