Условный LookUp с транспозицией

У меня есть следующая таблица в Excel 2010:

Code Variable 2003 2004 2005 923587 WC05301 0.300926921 0.665902164 0.916134943 923587 WC05001 0.715744225 0.453420519 0.312655924 923587 WC08376 0.247108211 0.374099217 0.378376177 923587 WC08001 0.947697297 0.686620132 0.41852137 923587 WC09204 0.78860597 0.246313221 0.556252026 923587 PTBV 0.400760313 0.637586519 0.723963115 923587 DPS 0.840353147 0.782413662 0.823206141 923587 WC02201 0.515966677 0.242782576 0.289028551 923587 WC03351 0.107782133 0.873951446 0.76152958 923587 WC02001 0.599825005 0.330610221 0.254449218 923587 WC02101 0.787267498 0.174806764 0.319124298 923587 WC02051 0.841697111 0.067667619 0.261424441 

Я хочу преобразовать эту таблицу в следующий формат:

 Code Year WC05301 WC05001 WC08376 WC08001 WC09204 PTBV DPS WC02201 WC03351 WC02001 WC02101 WC02051 923587 2003 923587 2004 923587 2005 923587 2006 923587 2007 923587 2008 923587 2009 923587 2010 923587 2011 923587 2012 923587 2013 923587 2014 

Я хочу найти первую пустую ячейку на пересечении (923587, WC05301,2003). Это дало бы формулу в виде:

 =INDEX(table,MATCH(Code&Year,A2:A&C1:H1,0),MATCH(Variable,B2:B)) 

Однако это не возвращает правильное значение. Я знаю, что это имеет какое-то отношение к обучению на A2:A&C1:H1 но как обойти / настроить для этого?

Проблема в том, что один диапазон поиска является вертикальным, а другой – горизонтальным. Я бы предложил использовать SUMPRODUCT и поставить все условия один за другим:

 =SUMPRODUCT($C$2:$E$13*($A$2:$A$13=$A16)*($C$1:$E$1=$B16)*($B$2:$B$13=C$15)) 

$C$2:$E$13 – это диапазон.

($A$2:$A$13=$A16) является первым условием соответствия кодов.

($C$1:$E$1=$B16) является вторым условием соответствия годам.

($B$2:$B$13=C$15) является последним условием соответствия переменных.

0 будет результатом, когда совпадений нет.

введите описание изображения здесь


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

Ты был почти там. Это работает:

=INDEX($C$2:$E$13, MATCH($A17&CHAR(1)&C$16,$A$2:$A$13&CHAR(1)&$B$2:$B$13,0), MATCH($B17,$C$1:$E$1,0))

введенный как формула массива, т.е. нажатие Ctrl-Shift-Enter. Затем эту формулу можно скопировать вниз и вправо.

Обратите внимание, как я использую CHAR(1) в качестве разделителя при подключении кода и переменной. Это гарантирует, что, например, код = 123WC + Variable = 456 и Code = 123 + Variable = WC456 не смешиваются. В качестве разделителя вы можете использовать любой символ, который, как вы уверены, никогда не появится в коде или переменной.

введите описание изображения здесь

  • Поиск, если две строки в отдельных столбцах существуют в одной строке в excel?
  • Поиск всей книги с VBA
  • Как заменить содержимое ячейки на основе заголовка строки?
  • Поиск появления пользовательских кодов в наборе данных
  • excel Поиск и список
  • Точное совпадение с использованием InStr в Excel VBA
  • найти соответствующее значение ячейки в другой книге и вернуть соответствие содержимому строки
  • EXCEL VBA: сделайте первую ячейку с частичным текстовым совпадением activecell с прогоном макросов, затем следующая ячейка вниз по списку с последующими запусками макросов activecell
  • Несоответствие типа VBA в методе поиска
  • Функция смещения работает только по горизонтали
  • Как найти общий диапазон в другом диапазоне в Excel
  • Interesting Posts

    Excel VBA – удаление повторяющихся строк, содержащих флажки (дубликаты из одного столбца)

    Сравнение диапазонов и копирование

    Дневное время для использования функции excel vba

    формат номера Excel – различные десятичные цифры

    Формулы POI Apache не оцениваются

    Создание макроса для сравнения значений ячеек на одном и том же рабочем листе, но в разных столбцах

    Экспорт SSRS в xlsx сломан?

    Ошибка времени выполнения 13: Несоответствие типов на подпункте

    запись значений в ячейку в строке в excel для переменной класса в python

    PHPExcel – Как изменить цвет, границу и фон диаграммы с помощью библиотеки phpexcel?

    Макрос вызов функции без необходимости (VBA)

    Функция Excel VBA для создания текста ячейки «BOLD» не будет работать

    Поместить итоговое значение во все ячейки группы

    Попытка найти данные во второй книге с помощью VBA

    Как передать и зациклить неопределенный массив функции в VBA?

    Давайте будем гением компьютера.