Поиск на основе двух критериев, одного числа и одного диапазона

Question

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

ID  Bottom Top  Percentage
1   1      50       0.3
1   51     75       0.4
1   76              0.5
2   1               0.75
3   1      25       1
3   26     100      2
3   101    1000     3
3   1001   2000     4
3   2001            5

Second List
ID  Total Amount
1   100
2   146
3   1256

Answer 1

Предполагая, что ваша первая таблица находится на Sheet1, а вторая - на Sheet2, и вы хотите заполнить столбец Amount второй таблицы, следующая формула выполнит это для первой ячейки Amount (ячейка C2) Sheet2. Для этого необходимо отсортировать первую таблицу по столбцу ID, а затем по столбцу Bottom. Эту формулу можно скопировать в конец таблицы 2 после ее ввода.

=VLOOKUP(B2, OFFSET(Sheet1!B:D, MATCH(A2, Sheet1!A:A, 0) - 1, 0, COUNTIF(Sheet1!A:A, A2)), 3)

Обратите внимание, что столбец Top на самом деле не нужен для этого.

Разбивая эту формулу, она использует функцию VLOOKUP для поиска в нижнем столбце ближайшего значения, меньшего или равного общему значению, а затем возвращает значение в столбце Процент для той же строки. Он также должен ограничивать поиск строками, соответствующими правильному идентификатору, и делает это с помощью функции OFFSET . Функция OFFSET использует строку первого совпадающего идентификатора, найденного с помощью функции MATCH и общее количество совпадающих идентификаторов, найденных с помощью функции COUNTIF .