Поиск в двумерном массиве Excel значения, возвращая значение соседней ячейки

Question

Я изучал этот сайт на протяжении последних двух уроков программирования и решил, что стоит присоединиться к этому невероятному сообществу!

Короче говоря, я хочу создать "создателя счетов", который позволяет сначала выбрать категорию с помощью раскрывающегося списка в A1, а затем элемент в этой категории с раскрывающимся списком в B1. Excel затем автоматически заполнит цену в следующем столбце C1.

По сути, 2 (не знаю, как это назвать: каскадные, вложенные, зависимые) выпадающие списки, которые при заполнении возвращают цену. Например:

  A        B    C
1 Category Item [Price]

Оба раскрывающихся списка используют проверку данных со ссылкой на определенное имя, основанное на массиве на листе "База данных". Этот лист отформатирован так:

category1 price category2 price category3 price category4 price ...
item1     $xx   item1     $xx   item1     $xx   item1     $xx
item2     $xx   item2     $xx   item2     $xx   item2     $xx

Я использовал эту колонку в основном потому, что она позволяет расширять каждую категорию до бесконечности. Если вы видите более простой способ, дайте мне знать!

По сути, чтобы напечатать цену в C1, моя цель - найти имя элемента в базе данных 2D Excel и сразу же вернуть ячейку справа от нее. В идеале формула в C1 будет использовать ячейку A1, чтобы определить, какую категорию искать, и искать в этом столбце соответствующее имя элемента в B1. Тогда он будет смещен на +1 вправо, ссылаясь на цену.

Что касается формул, у меня есть грубый псевдокод, но я запутался между VLOOKUP, OFFSET, MATCH и INDEX. В принципе:

=INDEX( "defined name of my entire database" , MATCH( B1, **column corresponding to chosen component** , 0 ) + 1 , MATCH( A1 , components , 0 ) )

На мой взгляд, INDEX будет возвращать значение строки + 1 элемент в данном столбце. Первый МАТЧ найдет строку, в которой найден элемент, а второй МАТЧ определит столбец, в котором находится категория.

Теперь мой вопрос: как мне сделать первый поиск в МАТЧЕ ТОЛЬКО по столбцу, который соответствует нужной категории?

Честно говоря, я понятия не имею, правильный ли мой псевдокод. Я перевел его в формулу, и он просто возвращает #N/A в ячейку.

Большое спасибо за любую помощь!!!

Answer 1

Я бы попробовал следующий подход:

Возьмите первый столбец таблицы базы данных и сместите его на столько столбцов вправо, насколько искомая категория смещена влево. Тогда у нас есть столбец цены этой категории, потому что мы сместили его на 1 столбец, если он соответствует первой категории, 3, если он соответствует второй категории, и так далее.

Из этого столбца укажите значение из строки, которое совпадает со строкой в ​​найденном столбце смещения-1 (столбец категории), который соответствует имени искомого элемента.