Найти нужное значение в таблице по условию

[VanBlack]

Здравствуйте! Столкнулся с такой проблемой (см. вложение)...
До "Боя за 2-е место" включительно формулы работают правильно и пары определяются верно (Спасибо за помощь пользователю Pelena). Но для определения пары "ПБ за 3-е место" принцип формулы использующийся для определения пары "ПБ за 2-е место" не подходит. Во всяком случае, у меня так и не получилось её изменить так, чтобы она работала верно, т.к. диапазон поиска может быть не один. Например, исходя из результатов в примере во вложении, нужно найти проигравших победителю "Боя за 2-е место", т.е. Неретину Фёдору, в предыдущих поединках (если они есть), для выявления из них победителя, чтобы он составил пару проигравшему "Боя за 2-е место", т.е. Извекову Евгению, для определения бронзового призёра. В данном случае это Лыков Илья, находящийся в диапазоне H15:H16, но если бы в "Бое за 2-е место" выиграл Извеков Евгений, первый проигравший ему был бы Смирнов Владислав, находящийся в диапазоне B14:B23. Исходя из этого и основываясь на принципах работы мозга, который верно определяет последующие пары, нужна формула для ячейки J15 следующего принципа поиска: В диапазоне H18:H19 найти значение ячейки, справа от которой стояло бы значение 1 (в данном случае Неретин Фёдор). Затем найти ячейку с таким же значением в диапазоне B14:B23 и если в ячейке справа значение 1 то отобразить значение нижней ячейки, если строка нечётная или верхней ячейки, если строка чётная. Если такового не обнаружится то искать в диапазоне D15:D22 и отобразить значение нужной ячейки по тому же принципу. Если и здесь такового не обнаружится, то искать в диапазоне H15:H16 (в данном случае поиск остановился бы здесь и в ячейке J15 отобразилось бы значение ячейки H16 - Лыков Илья). Если и здесь безрезультатно, то оставить значение пустым. Буду очень признателен всем, кто дочитал до конца и сможет как то помочь! Буду рад любым предложенным вариантам, способным найти верное значение!

[SAS888]

Может быть "формулисты" и предложат формулу из существующих в Excel, но я специализируюсь на VBA, поэтому могу предложить пользовательскую функцию.
Посмотрите пример во вложении. Пользовательская функция "ПБ_за_3" (в ячейке "J15") имеет 4 параметра:
1. Диапазон, из которого требуется взять фамилию для поиска.
2. Диапазон для 1-го поиска.
3. Диапазон для 2-го поиска.
4. Диапазон для 3-го поиска.
Не знаю, на сколько правильно я Вас понял. Проверяйте...

[VanBlack]

Спасибо за отличное предложение! Это гораздо лучше формулы! Единственный минус, но это моё упущение, чётная и не чётная строка имеется ввиду в указанном диапазоне, а не самом листе. Из-за этого функция иногда выдаёт не верные значения. Например, в новом вложении должно отобразится значение ячейки B23 (Милонов Александр), т.е. первого проигравшего победителю "Боя за 2-е место" (Неретину Фёдору). И ещё, как сделать, чтобы в ячейке J16 отобразилось значение второго проигравшего (в данном примере это был бы Азаров Данил)?

[SAS888]

Посмотрите пример во вложении. В функцию добавил 5-й параметр. Если "flag = 1", то будет найден 1-ый проигравший. Если "flag = 2", то 2-ой. Примеры функции в ячейках "J15" и "J16".
С четностью строк вроде тоже подправил. Проверяйте...

[VanBlack]

Всё супер! Только почему не реагирует, если я меняю значения в ячейках I18:I19, т.е. если допустим "В Бое за 2-е место" победит Чаборин Никита а не Неретин Фёдор? Ведь по условию "В диапазоне H18:H19 найти значение ячейки, справа от которой стояло бы значение 1" функция должна реагировать на это изменение?

[SAS888]

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

Код: Выделить всё

Application.Volatile

Пример во вложении. Там же я исправил обнаруженную неточность при определении четности строки в зависимости от диапазона, в котором выполнились условия поиска.

[VanBlack]

Отлично, сейчас работает как часы! Спасибо огромное!

[VanBlack]

Скажите, а можно ли эту функцию применить и для поиска пары для "Боя за 2-е место"? Если да, то что нужно изменить? Просто ваша функция, на мой взгляд, удобнее чем формулы.

[SAS888]

Можно. Объясните подробно: по каким условиям выбираются фамилии в конкретных ячейках...

[VanBlack]

Условия такие же, только искать надо проигравших победителю "Финала" и третий диапазон (H15:H16) здесь для поиска не нужен.
Если точнее, то: В диапазоне F18:F19 найти значение ячейки, справа от которой стояло бы значение 1 (в примере 4 это Лаврищев Александр). Затем найти ячейку с таким же значением в диапазоне B14:B23 и если в ячейке справа значение 1 то отобразить значение нижней ячейки, если строка в диапазоне нечётная или верхней ячейки, если строка в диапазоне чётная. Если такового не обнаружится, то искать в диапазоне D15:D22 и отобразить значение нужной ячейки по тому же принципу. В примере 4 это были бы Чаборин Никита (в ячейке H15, т.е. первый проигравший победителю финала) и Извеков Евгений (в ячейке H16, т.е. второй проигравший победителю финала).