Можно ли использовать Index and Match или что-то подобное в Microsoft Excel, чтобы найти последнюю заполненную строку чисел для двух наборов чисел в одном столбце?

Question

Образец изображения таблицы:

Цель: в образце таблицы я хочу извлечь из столбца C последний заполненный номер CST как 1235 и последний заполненный номер Vat как 204.

Если я использую = VLOOKUP("CST", B2:C5,2, FALSE), он возвращает первое значение CST, т.е. 1234. Как и для НДС, используя VLOOKUP возвращает 203.

Если я попробую Match, то отлично работает для CST, но для той же формулы НДС с меньшим lookup_value, скажем, 250 (так как значения НДС ниже 250), в результате получается # N/A.

Снимок экрана (с использованием MATCH с другим lookup_value в одном столбце):

Answer 1

Для чего-то вроде этого я предпочитаю AGGRAGATE, чтобы соответствовать:

=INDEX($B:$B,AGGREGATE(14,6,ROW($A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A)))/($A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A))=D$1),1))

Я поместил фактические критерии в D1 и D2, чтобы я мог ссылаться на них напрямую, а не на жесткий код "CST" и "VAT" в формуле, делая его перетаскиваемым.

Два $A$2:INDEX($A:$A,MATCH("ZZZ",$A:$A)) Динамически устанавливают диапазон ссылок, так как это формула типа массива. Он устанавливается от А2 до последней ячейки в столбце А, в которой есть текстовая строка.

Агрегат вернет наибольший номер строки (последняя строка), который соответствует критериям индекса.

Answer 2

Вы можете сделать это с LOOKUP .

LOOKUP Когда значение lookup_value больше любого числа в массиве, функция возвращает последнее число в массиве; конструкция 1/(1/(...)) преобразует 0 в ошибки, поэтому последний "номер" будет значением в той же позиции, что и последний CST или VAT зависимости от формулы.

Last CST:  =LOOKUP(9E+307,1/(1/(($B$2:$B$999="CST")*$C$2:$C$999)))
Last VAT:  =LOOKUP(9E+307,1/(1/(($B$2:$B$999="VAT")*$C$2:$C$999)))

Вышеприведенные формулы предполагают, что номер счета-фактуры всегда является числом, если это может быть строка, формула нуждается в некоторой модификации.

Last CST:  =LOOKUP(2,1/(($B$2:$B$999="CST")*ROW($B$2:$B$999)),$C$2:$C$999)
Last VAT:  =LOOKUP(2,1/(($B$2:$B$999="VAT")*ROW($B$2:$B$999)),$C$2:$C$999)

Answer 3

Используйте VLOOKUP для вертикальных поисков. Второй аргумент - это матрица. Если в строках нет ограничений, не указывайте номера строк, только буквы столбцов. Третий аргумент сообщает, какой столбец использовать. 2 означает второе, т.е. C в вашей матрице.

По умолчанию функция не останавливается при первом поиске; он переходит к поиску большего количества вхождений, в конечном итоге приводя к нахождению самой нижней записи (как правило, самой новой), именно так, как нужно.

Следовательно, используйте

=VLOOKUP("CST", B:C, 2)

а также

=VLOOKUP("VAT", B:C, 2)

Обратите внимание, что есть также HLOOKUP для горизонтальных поисков.