Google-sheets – How to extract data from a table dynamically

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-query

I have the following table

And I would like a formula that would produce the following result

I would like to iterate through the table going from left to right top to bottom and if there is a value in a cell, I would like to return with the matching top row criteria. How would I do this?

Best Answer

=ARRAYFORMULA(TRIM(SPLIT(TRANSPOSE(SPLIT(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF(C2:O<>"", "♦"&C2:O&"♠"&C1:O1, )),,999^99)),,999^99), "♦")), "♠")))

Related Solutions

Google-sheets – How to select a particular cell from an ‘array formula’ result

Please try:

=index(GOOGLEFINANCE("PG", "high", DATE(2014, 4, 1)),2,2)

Google Sheets – Return Array of Cells from a Table

One approach is to filter the table before querying it. Here is an example where the table is in A1:D7 and the choice of product is in cell F1:

=query({A1:A7, filter(B1:D7, B1:D1 = F1)}, "select * where Col2 > 0")

Here, filter keeps only the column where the column name matches F1. The array notation {} makes a two-column array with the first column coming from A1:A7 (ingredient names) and the second being quantities. Finally, query selects the rows where the quantity is positive.

And this is a more decorated version of the above, which displays "no recipe found" when there are no matches, and omits the column names in the output by using label ... ''.

=iferror(query({A1:A7, filter(B1:D7, B1:D1 = F1)}, "select * where Col2 > 0 label Col1 '', Col2 ''"), "no recipe found")

Best Answer

Related Solutions

Google-sheets – How to select a particular cell from an ‘array formula’ result

Google Sheets – Return Array of Cells from a Table

Related Topic