☰

Условный ВПР на основе дат

tag-icon • tag-icon microsoft-excel vlookup
Условный ВПР на основе дат

У меня есть две таблицы в Excel, которые я хочу объединить:

Дата Электронная почта
2019-07-07 06:49:10 почта1
2019-07-14 20:21:16 почта2
2019-06-23 18:22:40 почта3
2019-08-11 13:17:54 почта4

И

Дата Электронная почта Номер статьи
02/04/2019 15:11 почта1 № 1
08/08/2019 20:21 почта1 № 5
05/06/2019 16:30 почта2 № 1
07/09/2019 13:00 почта2 № 2
08/09/2019 12:43 почта2 № 3
05/05/2019 18:13 почта3 № 7
28/06/2019 09:00 почта4 № 6

Я хочу объединить эти таблицы по Email. Для 'mail3' и 'mail4' это должно быть довольно просто, они получают № 7 и № 6. Однако для 'mail1' и 'mail2' это сложнее. Я хочу объединить их, сравнив даты, так что они получат номер статьи, связанный с датой, ближайшей к нододата в таблице 1. Я попробовал обычный VLOOKUP для объединения по электронной почте, но не могу понять, возможно ли сделать это условно. Пожалуйста, дайте мне знать, если вам нужна дополнительная информация. Спасибо.

решение1

Я думаю, вам следует выбрать INDEXи MATCHвместе с AGGREGATE. Это комбо всегда как минимум так же быстро, VLOOKUPа в лучшем случае НАМНОГО быстрее!

Вот пример:

введите описание изображения здесь

Формула в C2:

=INDEX($G$2:$G$8,MATCH(AGGREGATE(14,3,($F$2:$F$8=B2)*($E$2:$E$8<A2)*($E$2:$E$8),1),$E$2:$E$8,0))

Тянуть вниз...

Чтобы ответить на вопросы @Rajesh ниже, это работает для любой строки, пока вы обновляете ссылки соответствующим образом, строка 1, 100, 1000, 100000. Это не имеет значения. Не забудьте использовать истинные значения даты и TRIMвыборочные данные, предоставленные OP:

введите описание изображения здесь

Формула в C182:

=INDEX($G$182:$G$188,MATCH(AGGREGATE(14,3,($F$182:$F$188=B182)*($E$182:$E$188<A182)*($E$182:$E$188),1),$E$182:$E$188,0))

решение2

Эта простая формула массива (CSE) находит ближайшее совпадение для даты и устраняет проблему:

введите описание изображения здесь

Отредактированная формула в Q2:

{=IF(ISBLANK(O2),"",IFERROR(INDEX($U$2:$U$9,MATCH(1,(P2=$T$2:$T$9)*(O2>=S$2:S$9),1)),""))}
  • Закончите формулу с помощьюCtrl+Shift+Enterи залейте его.

Примечание.

  • Соответствие гардеробу — это вопрос дискреционный, это может бытьGreater/Less than or Equals to the Criteria Date.

  • Соответственно, оператор сравнения в этой части формулы может быть изменен на (O2>=S$2:S$9).

При необходимости измените ссылки на ячейки в формуле.

Связанный контент