Это может быть сделано без использования VBA. Однако для этого требуются не только функции поиска / ссылки, поскольку они работают только в одном измерении (как и большинство других функций).
SUMPRODUCT() - это одна функция, которая работает с двумерными массивами (которые могут быть сгенерированы с помощью простого сравнения).
Решение требует только две следующие формулы:
Формула 1 Введена в B26 и введена ctrl / заполнена / скопирована в B26:C26:
=IF(ISERROR($D26),NA(),INDEX(B:B,SUMPRODUCT(MAX(($D$2:$N$21=$A26)*(ROW($D$2:$N$21))))))
Формула 2, введенная в D26:
=INDEX($1:$1,COLUMN($D$2:$N$21)-1+MATCH($A26,INDEX($D$2:$N$21,MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),1):INDEX($D$2:$N$21,MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),COLUMNS($D$2:$N$21)),0))
Предварительно подтвержденный вариант формулы 2:
=
INDEX(
($1:$1),
COLUMN($D$2:$N$21)-1
+MATCH(
$A26,
INDEX(
$D$2:$N$21,
MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),
1
)
:INDEX(
$D$2:$N$21,
MAX(1,SUMPRODUCT(MAX(($D$2:$N$21=A26)*(ROW($D$2:$N$21))))-ROW($D$2:$N$21)+1),
COLUMNS($D$2:$N$21)
),
0
)
)
Обратите внимание, что если бы суммы в долларах в таблице были уникальными, можно было бы использовать менее сложную формулу. Поскольку вы не указали, что вы хотели бы делать при обнаружении дубликатов, я написал простейшую формулу, которая все еще работает, когда они обнаруживаются.
Так получается, что эта формула извлекает самое левое совпадающее значение в самой нижней строке, содержащей совпадение. Его можно изменить, чтобы он возвращал определенные конкретные альтернативные значения.
Следующая формула может использоваться для определения наличия дубликатов в таблице для значения, введенного в A26 , если требуется какое-либо уведомление / действие:
=SUMPRODUCT(MAX((D2:N21=A26)*(ROW(D2:N21))))<>SUMPRODUCT(SUM((D2:N21=A26)*(ROW(D2:N21))))
Для случая уникальных значений, функция MAX() в формуле 1, конечно, больше не требуется, и упрощенная формула 2 будет иметь вид:
=INDEX($1:$1,IFERROR(1/(1/(SUMPRODUCT(($D$2:$N$21=A26)*(COLUMN($D$2:$N$21))))),NA()))
Заметки:
- Предварительно подтвержденная формула действительно работает, если введена.
- Квадратные скобки
($1:$1) в предварительно проверенной версии необходимы для того, чтобы заставить $1:$1 оставаться на своей собственной строке.
- Хотя я выбрал отображение ошибки
#N/A если введенная сумма в долларах не может быть найдена, ее можно изменить на что-либо еще.
