Как заставить VLOOKUP вернуть * последний * матч?

Вы можете использовать формулу массива для получения данных из последней соответствующей записи.

=INDEX(IF($A$1:$A$20="c",$B$1:$B$20),MAX(IF($A$1:$A$20="c",ROW($A$1:$A$20))))

Введите формулу, используя Ctrl+Shift+Enter.

Это работает как INDEX/MATCH строительство VLOOKUP, но с условным MAX используется вместо MATCH,

Обратите внимание, что это предполагает, что ваша таблица начинается в строке 1. Если ваши данные начинаются в другой строке, вам необходимо настроить ROW(...) часть, вычитая разницу между верхним рядом и 1.

(Ответ здесь как отдельный вопрос для отсортированных данных.)

Если бы данные были отсортированы, вы могли бы использовать VLOOKUP с range_lookup аргумент TRUE (или опущено, так как это значение по умолчанию), которое для Excel официально описывается как "поиск приблизительного соответствия".

Другими словами, для отсортированных данных:

• установка последнего аргумента FALSE возвращает первое значение и
• установка последнего аргумента TRUE возвращает последнее значение

Это в значительной степени недокументировано и неясно, но относится к VisiCalc (1979), и на сегодняшний день действует по крайней мере в Microsoft Excel, LibreOffice Calc и Google Sheets. Это в конечном итоге связано с первоначальной реализацией LOOKUP в VisiCalc (и оттуда VLOOKUP а также HLOOKUP), когда не было четвертого параметра. Значение определяется с помощью бинарного поиска с использованием включающей левой границы и исключительной правой границы (распространенная и элегантная реализация), что приводит к такому поведению.

Технически это означает, что поиск начинается с интервала кандидата [0, n), где n длина массива, и условие инварианта цикла состоит в том, что A[imin] <= key && key < A[imax] (левая граница <= цель, правая граница, которая начинается одна после конца, является> целью; для проверки либо проверки значений в конечных точках до, либо проверки результата после) и последовательного деления пополам и выбора того, какая сторона сохраняет этот инвариант: путем исключения одна сторона будет, пока вы не дойдете до интервала с 1 слагаемым, [k, k+1)и алгоритм затем возвращает k, Это не обязательно должно быть точное совпадение (!): Это просто самое близкое совпадение снизу. В случае повторяющихся совпадений это приводит к возвращению последнего совпадения, поскольку требует, чтобы следующее значение было больше ключа (или конца массива). В случае дубликатов вам нужно некоторое поведение, и это разумно и легко реализовать.

Это поведение явно указано в этой старой статье базы знаний Майкрософт (выделение добавлено): "XL: Как вернуть первое или последнее совпадение в массиве" ( Q214069):

Вы можете использовать функцию LOOKUP() для поиска значения в массиве отсортированных данных и возврата соответствующего значения, содержащегося в этой позиции, в другом массиве. Если значение поиска повторяется в массиве, оно возвращает последнее найденное совпадение. Такое поведение верно для функций VLOOKUP(), HLOOKUP() и LOOKUP().

Официальная документация для некоторых таблиц приведена ниже; ни в одном из них не указано поведение "последнее совпадение", но оно подразумевается в документации Google Sheets:

• Майкрософт Эксель

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

• Google Sheets:

  Если is_sorted является TRUE или опущено, возвращается ближайшее совпадение (меньше или равно ключу поиска)

Если значения в массиве поиска являются последовательными (т. Е. Вы ищете наибольшее значение, например, самую позднюю дату), вам даже не нужно использовать функцию INDIRECT. Попробуйте этот простой код:

=MAX(IF($A$1:$A$20="c",$B$1:$B$20,)

Снова введите формулу, нажав CTRL + SHIFT + ВВОД.

=LOOKUP([payee field] , [payee range] , [category range])

Это даст вам последнее значение

Получу ли я бонусные баллы за опоздание на 3 года?

Просто переверните стол вверх дном.

Тогда первый совпадающий элемент действительно будет последним, как вы пожелаете.

Как перевернуть его вверх дном? Ну, я не имею в виду буквально делать это!!

(Сегодня, в 2021 году, мы бы просто использовалиXLOOKUP()и скажите ему, чтобы он выглядел от начала до конца, а не от начала до конца. Но читайте дальше, чтобы найти решение, которое хорошо сработало бы в 2014 году. Или в 2004, или в 1994 году.)

Как вы, возможно, знаете, а можете и не знать, функция может переупорядочивать столбцы в заданном ей диапазоне так, чтобы в выводе был столбец, который был справа от другого столбца, теперь слева от него. Или позвольте вам выбрать только некоторые столбцы или использовать один столбец более одного раза. Это дает вам такую ​​​​же возможность со строками. Люди редко задумываются о ссорах, хотя, если им нравится Стивен Кайнд, они наверняка думают о «Том, кто ходит за ссорами»…

И вообще, как перевернуть строки? Хитрость в 2014 году, позволяющая убедиться, что все строки в диапазоне присутствуют в результате (когда это было необходимо, когда оставление значения пустым или «0» не работало, что в основном происходило из-за использования специального выбора для столбцов) было использовать значение (если в диапазоне тысяча строк). Вариации на эту тему существовали, но они сводились к использованию вычислений для создания строки иINDIRECT()сделать это чем-тоROW()бы использовать. Я покажу это в следующей формуле, но в 2021 году мы сможем просто создать необходимую последовательность от высокого к низкому.

Проблема с написанием какROW(1000:1)попытка получить последовательность, понижающуюся по одному за раз от 1000 до 1 (1000, 999, 998, 997,...3, 2, 1), заключается в том, что Excel с радостью берет ваш «неправильный» диапазон адресов и «услужливо» исправляет его для вас, чтобы записывалось как 1:1000, а не 1000:1... независимо от того, что вы делаете.

Но... вы знаете, сколько там рядов. Вы указали диапазон, чтобы вы могли определить, сколько строк. Даже если вы дали ему именованный диапазон. Поскольку вы можете, вы можете добавить 1 к этому значению, а затем вычестьROW(1:1000)что Excel примет. В этом примере это будет 1000 строк, поэтому вы используете 1001 следующим образом:

      1001 - ROW(1:1000)

поэтому Excel вычисляет 1001-1 (1000), 1001-2 (999), 1001-3 (998) и так далее. Видите, как получается обратная последовательность?

Используйте это для параметра строки (второй параметр) в вашемINDEX()функция. Теперь ваш стол перевернут.

Поскольку вы сделали это для строк, вам также придется указать столбцы. Поскольку у вас всего два столбца, используйте простую константу массива{1,2}для этого, как вы хотите, в таком порядке. (В настоящее время вы можете использовать (нормальный подъем)SEQUENCE()и для этого тоже.)

Теперь вся ваша таблица перевернута, и вы используете ее в качестве второго параметра в своемVLOOKUP(), диапазон поиска.

И первое найденное значение на самом деле является последним, потому что в перевернутой таблице вы ищете от последнего к первому.

У меня была попытка в наиболее частом значении. Не уверен, что это будет работать в libreOffice, но, похоже, работает в Excel

= ИНДЕКС ($B$2:$B$9,MATCH(МАКС (-($A$2:$A$9=D2)*COUNTIFS($B$2:$B$9,$B$2:$B$9,$A$2:$A$9,D2)),- ($ A $ 2: $ A $ 9 = D2) * COUNTIFS ($ B $ 2: $ B $ 9, $ B $ 2: $ B $ 9, $ A $ 2: $ A $ 9, D2,),0))

Столбец A будет получателем, столбец B будет категорией, D2 - получатель, по которому вы хотите фильтровать. Я не уверен, почему он добавляет дополнительные разрывы строк в функцию выше.

Моя функция найти последнюю ячейку будет выглядеть следующим образом:

= НЕПРЯМОЙ ("B" & MAX(-($A$2:$A$9=D2)*ROW($A$2:$A$9)))

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

Обе эти функции должны быть введены с помощью Ctrl+ Shift+ Enter

Ты получил #DIV/0! ошибки, потому что вы должны написать свою формулу, как:

=LOOKUP(2;IF(([payee range] = [search value]);1;"");[category range])

это будет работать и найдет последний матч.

([payee range] = [search value]): булева матрица ИСТИНА / ЛОЖЬ

IF(([payee range] = [search value]);1;""): псевдобулева матрица 1 / ""

=LOOKUP(2; {pseudo-boolean matrix 1/""} );[category range]): всегда возвращайся последним 1 позиция