Excel: INDEX(, MATCH(),)) работает с диапазонами, но не с данными, отформатированными как TABLE

Question

Установка:

У меня есть два листа Excel с данными, оба "отформатированные в виде таблицы". Один представляет собой большой набор данных со многими строками и столбцами (полями), а другой - своего рода "словарь", содержащий всего 2 строки и 2 столбца (поля).

Таблица1 (набор данных)

+----------------------------------------------------+
| month | week | productName | price     | sold pcs. |
+---------------------------------------------------+|
| jan   | 1    | heavy       | (formula) | 25        |
| jan   | 2    | heavy       | (formula) | 51        |
| jan   | 3    | heavy       | (formula) | 06        |
| jan   | 4    | heavy       | (formula) | 00        |
| jan   | 1    | light       | (formula) | 39        |
| jan   | 2    | light       | (formula) | 11        |
| jan   | 3    | light       | (formula) | 98        |

Таблица 2 (дикт)

+---------------------+
| productName | price |
+---------------------+
|  heavy      | 125   |
|  light      | 65    |

Что я должен сделать:

Что мне нужно сделать, это перенести значения (цены на товары) из словаря в большой набор данных, где совпадают определенные значения (названия продуктов).

Что я пробовал:

=INDEX(Table2[@price], MATCH(Table1[@productName], Table2[@productName], 0))

При работе с диапазонами эта комбинация отлично работает. Однако, когда я делаю это таким образом, с данными, отформатированными в виде таблиц, они совпадают только в первой строке таблицы Table1.

Вот результат, который я получаю:

+-------------------------------------------------+
| month | week | productName | price  | sold pcs. |
+-------------------------------------------------+
| jan   | 1    | heavy       | 125    |  25       |
| jan   | 2    | heavy       | #N/A   |  51       |
| jan   | 3    | heavy       | #VALUE |  06       |
| jan   | 4    | heavy       | #VALUE |  00       |
| jan   | 1    | light       | #VALUE |  39       |
| jan   | 2    | light       | #VALUE |  11       |
| jan   | 3    | light       | #VALUE |  98       |

Первая строка правильно сопоставлена, вторая строка (снова такая же, как первая) не найдена, и оттуда начинаются значения ошибок. Что я должен делать?

Спасибо

Answer 1

Вы неправильно используете названные диапазоны:

• Table2[@productName] указывает на один элемент в таблице
• Table2[productName] указывает на весь столбец
• Table2 указывает на все столбцы и строки таблицы, исключая заголовки

Вы можете сделать это легко с VLOOKUP:

=INDEX(Table2,MATCH([@productName],Table2[productName],0),MATCH("price",Table2[#Headers],0))

Альтернативные решения:

=VLOOKUP([@productName],Table2,2,0)

=VLOOKUP([@productName],Table2,MATCH("price",Table2[#Headers]),0)

Или, если каждое productName уникально, вы можете использовать SUMIF:

=SUMIF(Table2[productName],[@productName],Table2[price])

Answer 2

Это должно работать для вас:

=INDEX(Table2[[#All],[Price]],MATCH([@productName],Table2[[#All],[ProductName]],0),1)

Заметка. использование #ALL, это выбирает весь столбец, а не только строку.