Поиск правильного диапазона от таблицы excel

Изображение из таблицы

Каков наилучший способ найти правильный столбец для пройденных миль с использованием визуального базового кодирования или некоторой функции excel и вернуть цену из этой колонки? HLOOKUP нельзя использовать здесь, потому что значение поиска не является точным, а диапазоны в таблице также не имеют определенных интервалов (если бы они были, я мог бы использовать, например, FLOOR(travelled miles/100)*100 и найти цену с HLOOKUP ). Очевидно, что легко найти цену вручную с помощью небольшого стола, но с большим столом компьютер будет быстрее.

Заметим, что если x находится между a и b , то MEDIAN(x,a,b)=x . Объедините это с некоторыми вложенными IF:

 =IF(MEDIAN(B5,B1,C1-1)=B5,B2,IF(MEDIAN(B5,C1,D1-1)=B5,C2,IF(MEDIAN(B5,D1,E1-1)=B5,D2))) 

Я нахожусь на своем телефоне, поэтому просто делал первые три случая, но, надеюсь, вы можете увидеть, как это продолжается.

(обратите внимание, что вам нужно удалить тире, чтобы это работало)

Редактировать:

Я также хочу ответить на ваш вопрос в комментариях выше. Вы можете использовать следующее, чтобы сохранить тире, но получить номер для работы.

Предположим, что ячейка A1 получила значение 10- . Мы можем использовать функцию FIND для разработки, где есть, и затем использовать функцию LEFT чтобы возвращать символы только до тире:

 =LEFT(A1,FIND("-",A1)-1) 

Это вернет значение 10 , но оно вернет его как строку, а не цифру – в основном Excel будет думать, что это текст. Чтобы заставить Excel рассматривать его как число, мы можем просто умножить значение на единицу. Поэтому наша формула выше:

 =(LEFT(A1,FIND("-",A1)-1))*1 

Вы также можете увидеть, что люди используют знак двойного минуса, например:

 =--LEFT(A1,FIND("-",A1)-1) 

Я не рекомендую это, потому что он немного сложный, но комбинирование с приведенной выше формулой дало бы:

 =IF(MEDIAN(B5,--LEFT(B1,FIND("-",B1)-1),--LEFT(C1,FIND("-",C1)-1)-1)=B5,B2,IF(MEDIAN(B5,--LEFT(C1,FIND("-",C1)-1,--LEFT(D1,FIND("-",D1)-1-1)=B5,C2,IF(MEDIAN(B5,--LEFT(D1,FIND("-",D1)-1,--LEFT(E1,FIND("-",E1)-1-1)=B5,D2))) 
  • Можно ли комбинировать формулы AND () и OR () в формулах Excel?
  • Скопируйте строки с Qty> 0
  • Копирование / вставка ячеек с фигурами
  • Объединение нескольких книг в одно рабочее тетрадь со всеми рабочими книгами в виде листов
  • Excel 2010: как фильтровать сводную таблицу на основе списка на другом листе
  • Как найти, когда достигнуты две цели и дата возвращения?
  • Excel 2010 VBA: установите цвет ячейки ячейки ячейки в предыдущем столбце
  • Excel 2010 - устранение конфликтов автофильтра с установкой цвета шрифта в VBA
  • Суммируйте диапазон столбцов
  • Проверить равные даты с помощью функции if
  • Создать таблицу просмотра Excel?
  • Давайте будем гением компьютера.