Индекс соответствия по нескольким столбцам

Question

У меня есть два листа один: с двумя столбцами мест и третий столбец с пробегом между этими местами:

На другом листе у меня есть выпадающий список мест в столбце A листа 1 и другой столбец с выпадающим списком мест в столбце B листа 1, затем третий столбец, который соответствует индексу, ищет пробег из столбца C листа 1.

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

Например, я хочу найти соответствие Office для Apple Blossom и Apple Blossom для Office.

Сейчас я использую формулу:

=ArrayFormula(iferror(ArrayFormula(INDEX('Copy of Location to Location'!C:C, MATCH('Travel Form 17-18'!B8&'Travel Form 17-18'!C8,'Copy of Location to Location'!A:A&'Copy of Location to Location'!B:B,0))),""))

но это только смотрит на Office в Apple Blossom, а не наоборот. Можно ли добавить еще одну формулу соответствия, которая будет искать оба столбца, не вводя данные в оба столбца на листе 1?

Answer 1

Первое правило ракетостроения: простые вещи легче, чем сложные.

Я воссоздал вашу проблему на одном листе, поэтому мне не пришлось бы везде использовать имена листов.  Столбцы A - C соответствуют столбцам A - C на вашем Листе 1 (он же 'Copy of Location to Location'), а столбцы AA - AE соответствуют столбцам A - E на вашем Листе 2 (он же 'Travel Form 17-18').  Я сократил вашу формулу (которую вы используете в Sheet2!E8) для

=IFERROR(INDEX(C:C, MATCH(AB8&AC8, A:A&B:B, 0)), "")

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

И логика не ракетостроение.  Если FROM&TO отсутствует в таблице «Location to Location», мы хотим найти TO&FROM:

=IFERROR(INDEX(C:C, IFERROR(MATCH(AB8&AC8, A:A&B:B, 0), MATCH(AC8&AB8, A:A&B:B, 0))), "")

это формула у меня в ячейке AE8 на этом скриншоте:

     

Мы, очевидно, используем разные версии Excel.  Я не могу сказать ArrayFormula(…) в моем (Excel 2013); Я просто набираю Ctrl+Shift+Enter после формулы, чтобы сделать ее формулой массива.  Так что я не знаю точно, как работает этот ArrayFormula(…) (вы уверены, что вам нужно использовать его дважды в формуле?).  Но вот мое решение (сверху) переведено обратно на ваш лист и названия столбцов:

=IFERROR(INDEX(C:C, IFERROR(MATCH('Travel Form 17-18'!B8&'Travel Form 17-18'!C8, 'Copy of Location to Location'!A:A&'Copy of Location to Location'!B:B, 0), MATCH('Travel Form 17-18'!C8&'Travel Form 17-18'!B8, 'Copy of Location to Location'!A:A&'Copy of Location to Location'!B:B, 0))), "")

Я дам вам понять, где там нужно сказать ArrayFormula(…) .