Найдите 2 значения, которые должны существовать в строке, а затем верните значение из другого столбца.

Question

У меня есть значения в столбцах A, B и C:

1 - 1 - 2

1 - 2 - 3

2 - 2 - 4

3 - 4 - 1

2 - 3 - 5

и другой столбец (предположим, это в столбце E):

1

1

2

3

4

Примечание: все столбцы начинаются со строки 1.

Я хочу найти строку, которая содержит значение E1 в диапазоне столбца A и значение E2 в диапазоне столбца B. Если строка содержит оба значения, вернуть значение в столбце C

Я создал следующую функцию в столбце F:

= ЕСЛИ (И (Е1 = $ A $ 1:$ A $ 4, Е2 = $ B $ 1:$ B $ 4), $ C $ 1:$ C $ 4)

это работает, только если значения, которые соответствуют моим критериям, находятся в одной строке, в противном случае я получил false. Пример для разработки проблемы:

EFABC

1 2 ---------------------> 1 1 2: потому что E1 = A1 и E2 = B1 - F1 будет = 2

1 3 ---------------------> 1 2 3: потому что E2 = A2 и E3 = B2 - F2 будет = 3 2 ложным ------ -----------> 2 2 4: потому что E3 = A3, но E4!= B3 - F3 ложно (должно быть 5)

Любое предложение?

Answer 1

Попробуйте это:

=INDEX(C:C,MATCH(1,(E1=A:A)*(E2=B:B),0))

К сожалению, я не могу взять кредит на себя, как это видно из этой прекрасной статьи на exceljet.net

(Наиболее) релевантная часть, с некоторыми исправлениями опечаток:

Как работает эта формула:

Обычно формула INDEX MATCH настроена с MATCH, установленным для просмотра диапазона в один столбец и обеспечения соответствия на основе заданных критериев. Без объединения значений во вспомогательном столбце или в самой формуле невозможно предоставить более одного критерия.

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

Временный массив единиц и нулей основан на этом фрагменте:

(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

Здесь мы сравниваем элемент H5 со всеми элементами, размер в H6 со всеми размерами и цвет в H7 со всеми цветами. Первоначальный результат выглядит так:

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}*{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}*{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

Операция умножения преобразует значения TRUE FALSE в 1 и 0:

{1;1;1;0;0;0;1}*{0;0;1;0;0;1;0}*{1;0;1;0;0;0;1}

И конечный результат выглядит так:

{0;0;1;0;0;0;0}

Который входит в MATCH как массив поиска:

MATCH(1,{0;0;1;0;0;0;0})

MATCH возвращает 3, и вся формула сводится к стандартной формуле INDEX MATCH

=INDEX(E5:E11,3)

с конечным результатом $ 17,00.

Answer 2

Вы можете использовать эту формулу Array:

{=IFERROR(INDEX($C$1:$C$5,SMALL(IF(($A$1:$A$5=E1)*($B$1:$B$5=E2),ROW($1:$5)-ROW($1:$1)),ROW($A1))),"")}

NB

• Завершите формулу с помощью Ctrl+Shift+Enter .
• При необходимости измените ссылки на ячейки в формуле.