Формула ПОИСКПОЗ — это мощный инструмент Excel, позволяющий найти относительное положение указанного значения в диапазоне. Однако при работе со сценариями, когда в совпадении имеется несколько ячеек и некоторые из них пусты, а другие нет, формулу необходимо адаптировать. В этой статье мы рассмотрим различные методы решения таких ситуаций, а также приведем примеры кода.

Метод 1: игнорирование пустых ячеек
Один из подходов заключается в изменении формулы ПОИСКПОЗ, чтобы игнорировать пустые ячейки и учитывать только непустые ячейки. Этого можно добиться, используя функции ЕСЛИ и ЕСПУС в сочетании с функцией ПОИСКПОЗ. Вот пример:

=MATCH(TRUE,INDEX((A1:A10<>"")*(B1:B10<>""),0),0)

Метод 2: учет как пустых, так и непустых ячеек.
Если вы хотите включить в сопоставление как пустые, так и непустые ячейки и назначить приоритет непустым ячейкам, вы можете использовать функцию ЕСЛИОШИБКА.. Этот метод присваивает более высокое значение непустым ячейкам, обеспечивая им приоритет при сопоставлении. Вот пример:

=MATCH(TRUE,IFERROR(A1:A10<>"",FALSE)*IFERROR(B1:B10<>"",TRUE),0)

Метод 3: обработка нескольких критериев
В ситуациях, когда у вас есть несколько критериев и вы хотите сопоставить ячейки, удовлетворяющие всем условиям, вы можете использовать функцию СУММПРОИЗВ. Этот метод позволяет применять к диапазону несколько условий и возвращать позицию соответствующей ячейки. Вот пример:

=MATCH(1,SUMPRODUCT((A1:A10="criteria1")*(B1:B10="criteria2")),0)

Метод 4: Решение VBA
Если сложность ваших требований превышает возможности формул Excel, вы можете прибегнуть к использованию Visual Basic для приложений (VBA). VBA позволяет создавать собственные функции или макросы для управления процессом сопоставления. Вот пример функции VBA:

Function CustomMatch(rng As Range, criteria As Variant) As Long
    Dim cell As Range
    Dim i As Long
    i = 1
    For Each cell In rng
        If cell.Value = criteria Then
            CustomMatch = i
            Exit Function
        End If
        i = i + 1
    Next cell
    CustomMatch = CVErr(xlErrNA)
End Function