VLOOKUP с условиями IF выполняется

У меня есть эта проблема на рабочем месте, чтобы заполнить рабочий лист нужным номером.

Sheet 1: (Report) SSN | Service Date 123456 | 10/01/2014 Sheet 2: (Data) SSN | Case Number | Start Date | End Date 123456 | 0000000 | 01/01/2010 | 12/31/2012 123456 | 1111111 | 01/01/2013 | 05/31/2014 123456 | 2222222 | 06/01/2014 | 11/10/2015 

Как я могу сделать VLOOKUP на основе даты службы в пределах «диапазона» даты начала и окончания другого листа?

В этом случае я хотел бы 2222222 SSN и вернуть номер дела 2222222 потому что это так активно для такой даты службы.

Я смотрел онлайн и нашел «MATCH». Я могу сопоставить первый результат этого случая с SSN , но как перейти к следующему случаю, если он не соответствует?

 =IF(E2>=INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),4)&E2<=INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),5),"YES","NO") 

Я использую Excel 2013 на Windows 7 на работе.

Если SSN находится в A1 обоих листов, и ваш Case Number s является числовым (кроме 0000000 ), вы можете попробовать:

 =SUMIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!C:C,"<="&B2,Sheet2!D:D,">="&B2) 

SUMIFS объясняется здесь (и в другом месте!).

Вам потребуются 3 условия. a) Является ли дата начала менее, чем Дата службы. b) Является ли дата окончания больше даты службы и c) соответствуют ли номера SSN?

Используйте новую функцию AGGREGATE¹, чтобы принудительно выполнить какие-либо несоответствия в состоянии ошибки, используя параметр игнорирования ошибок (например, 6 ), чтобы отбросить ошибки.

 =INDEX(Sheet2!$B$2:$B$9999, AGGREGATE(15, 6, ROW($1:$9998)/((Sheet2!C$2:C$9999<=B2)*(Sheet2!D$2:D$9999>=B2)*(Sheet2!A$2:A$9999=A2)), 1)) 

Несколько критериев VLOOKUP

Для всех целей и целей формула рабочего листа рассматривает FALSE как ноль (например, 0 ) и TRUE как один (например, 1 ). Любое число, умноженное на ноль, равно нулю, а любое число, умноженное на одно, – это то же самое число. Функция AGGREGATE возвращает позицию позиции первого совпадения в Sheet2! B2 «B9999. Эта позиция строки будет числом где-то внутри ROW (1: 9998). Любая из строк, которые не соответствуют всем трем условиям, будет иметь по крайней мере один нуль, умноженный на знаменатель, и это означает, что знаменатель равен нулю. Все, что делится на ноль, приводит к ошибке #DIV/0! и AGGREGATE будет отбрасывать те из набора результатов. Опция AGGREGATE 15 – это МАЛАЯ, а последняя 1 – порядковый номер k для SMALL (самый маленький). Таким образом, из всех строк, соответствующих всем трем условиям, AGGREGATE возвращает наименьшую функцию в функцию INDEX, которая извлекает значение из Sheet2! B2 «B9999.

Затяните диапазоны до максимум 5 строк и используйте команду Evaluate Formula, чтобы выполнить формулу и получить лучшее понимание.

Возможно, стоит обратить внимание, что преобразовать эту формулу очень легко, чтобы получить второе, третье и т. Д., Так как это требует только упорядочения порядка k .


¹ Функция AGGREGATE была введена в Excel 2010. Она недоступна в более ранних версиях.

Эта массив-формула всегда будет печатать последнее совпадение:

 =INDEX(Sheet2!B:B,MAX((Sheet2!A:A=A2)*(Sheet2!C:C<=B2)*(Sheet2!D:D>=B2)*ROW(A:A))) 

Это формула массива и должна быть подтверждена с помощью Ctrl + Shift + Enter .

  • Он работает, если есть несколько решений, которые соответствуют критериям
  • Он также работает со всеми типами данных, которые вы хотите показать (значения / даты / строки)

! Однако вы должны сократить диапазон как можно короче. (его огромный расчет для всего листа)

  • Использование Vlookup / Макросов для транспонирования
  • Показывать конкретные ячейки, если несколько выпадающих точек равны
  • Использование динамического номера столбца для vlookup
  • Соответствие длины поиска соответствия для соответствия с началом значения поиска
  • Проблемы с Vlookup по энергозатратам?
  • VBA Variable Workbook Reference Runtime-Error 9
  • Поиск нечетких совпадений в Excel
  • Идеальная концепция для поиска в Excel - использование высоких и низких значений для определения диапазонов ZIpCode, относящихся к территориям
  • Excel VLOOKUP между двумя листами
  • Функция VLOOKUP работает отлично для более тысячи ячеек, а затем перестает работать
  • перечислить список соответствия для Vlookup в список
  • Давайте будем гением компьютера.