У Джона есть таблица поиска возрастающих числовых значений в столбце A и соответствующих текстовых значений в столбце B. Когда он использует функцию VLOOKUP, она возвращает текстовое значение для числового значения, равного или меньшего, чем указанное значение поиска. Джон действительно хочет, чтобы первое значение было больше или равно искомому.

Невозможно изменить способ работы ВПР; оно всегда будет соответствовать значению, равному или меньшему, чем значение поиска. Однако можно изменить формулу, используемую для фактического поиска. Рассмотрим следующую формулу, в которой предполагается, что значение, которое вы хотите использовать для поиска, находится в ячейке D1:

=IF(VLOOKUP(D1,$A$1:$A$10,1)=D1,VLOOKUP(D1,$A$1:$B$10,2), INDEX($B$1:$B$10,1+MATCH(D1,$A$1:$A$10)))

Если значение в D1 является точным совпадением со значением в столбце A, то используется обычная формула ВПР. Если это не так, то от ВПР отказываются в пользу функции ИНДЕКС в сочетании с функцией ПОИСКПОЗ.

Если вы можете отсортировать таблицу данных в порядке убывания, вы можете использовать более короткую формулу:

=INDEX($A$1:$B$10,MATCH(D1,$A$1:$A$10,-1),2)

Функция ПОИСКПОЗ использует значение D1 для поиска наименьшего значения, которое больше или равно этому значению. (Это то, что указывает параметр -1.) Функция ПОИСКПОЗ возвращает номер соответствующей строки, и затем он используется ИНДЕКС для фактического извлечения значения.

Другой интересный подход к проблеме — использовать обратные значения поиска в качестве столбца управления, который будет использоваться для фактического поиска информации из таблицы данных. Например, предположим, что ваша таблица данных находится в формате A1: B10 с фактическими числовыми значениями, которые вы используете для поиска в столбце A. Вам необходимо вставить столбец слева от вашей таблицы данных. В первую ячейку этого нового столбца (теперь столбец A) вставьте следующую формулу:

=1/B1

Это обеспечивает обратное значение в B1, и вы можете скопировать его вниз по ячейкам в столбце A. Теперь ваша таблица данных имеет три столбца, A1: C10.

Затем отсортируйте таблицу данных на основе этого нового столбца в порядке возрастания.

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

=VLOOKUP(1/E1,$A$1:$C$10,3)

Фактически вы получите желаемое значение из столбца C, связанное со значением в столбце B, которое равно или меньше значения в E1.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (3090) применим к Microsoft Excel 97, 2000, 2002 и 2003.