Как найти значение в таблице с несколькими критериями

Question

У меня есть лист данных с несколькими значениями в нескольких столбцах. У меня есть кол-во и текущая цена, которая при умножении дает мне текущий доход (CurRev).

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

Я хочу построить новый столбец, который проверяет "Product", "Tier" и «Location /State» и дает мне новую цену из справочной таблицы (выше), а затем умножает ее на qty.

например, данные

> Product,  Tier,  Location, Qty, CurRev,   NewRev 
> Product1, Tier1, VIC,      2,   $1000.00, $6000 (2 x $3000) 
> Product2, Tier3, NSW,      1,   $100.00,  $200 (1 x $200)
> Product1, Tier3, SA,       5,   $250.00,  $750 (5 x $150)
> Product3, Tier1, ACT,      5,   $100.00,  $500(5 x $100)
> Product2, Tier3, QLD,      2,   $150.00,  $240 (2 x $240)

В худшем случае, если я просто получу новую ставку, я смогу создать еще один столбец.

Answer 1

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

=INDEX(C$2:J$10,MATCH(1,INDEX((A$2:A$10=A18)*(B$2:B$10=B18),0),0),MATCH(C18,C$1:J$1,0))*D18

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

Answer 2

Это решение делает два больших предположения о ваших данных: 1) каждый продукт имеет ровно три уровня; и 2) идентификаторы продуктов в таблице поиска находятся в объединенных ячейках. Пожалуйста, дайте мне знать, если это не так.

Вот формула, которая даст вам значение NewRev для записи в строке 18.

=VLOOKUP(B18,INDIRECT("B"&MATCH(A18,$A$1:$A$10)&":J"&MATCH(A18,$A$1:$A$10)+2),MATCH(C18,$A$1:$J$1,0)-1,FALSE)*D18

Эта формула использует функцию INDIRECT чтобы определить, какую часть таблицы поиска использовать для вашего VLOOKUP на основе идентификатора продукта. Номер столбца для VLOOKUP достигается с помощью функции MATCH чтобы найти подходящее местоположение.