Create a table using the header and descriptions columns from a list that has multiple values-CodePudding

how can I use index and match to fill out a table based on header and items? I want to make a table that get the quantity of items per site, from my table that has info all mixed.

Is INDEX and MATCH the best way ?

the table I want to make:

ITEMS | SITE1 | SITE2 | SITE3| .... | SITE n |
----------------------------------------------
ITEM1 |   3   |   1   |      |      |        |
----------------------------------------------
ITEM2 |       |   3   |      |      |        |
----------------------------------------------
ITEM3 |   2   |       |      |      |        |
----------------------------------------------

My data table:

SITE  |  ITEM  | price | quantity  |
------------------------------------
site1 | item1  | XX    |     3     |
------------------------------------
Site1 | item3  | XX    |     2     |
------------------------------------
site2 | item1  | XX    |     1     |
------------------------------------
site2 | item2  | XX    |    3      |

CodePudding user response：

Since the value sought is numeric, and there are multiple conditions, an array formula is the simplest solution.
Per image below: Assuming the data locations were as showing the image, the following would be the formula as it appears in cell B2:

{=SUM(IF($A$8:$A$13=B$1,IF($B$8:$B$13=$A2,$D$8:$D$13)))}

The formula gets the numeric value (via SUM) from column D in the table where site (column A) matches the corresponding value in row 1, and item (column B) matches the corresponding value in column A of the 'value sought' table.

Sample, as implemented

Notes:
o You don't enter the {}. Instead, use Ctrl, Shift and Enter to enter an Array formula
o The formula will work just as well with the data table in a separate sheet
o Array formulas generally return faster that lookup functions