Как вернуть значение смещения - Vlookup

(Проверено на Excel 2010)

=INDEX(OFFSET(INDEX($A:$D,MATCH($F$2,$A:$A,0),0),0,0,5,4),MATCH("Signon",$D:$D,0),MATCH("To",$3:$3,0))

Предположения:

• Номер элемента списка (1234569) находится в столбце A.
• $F$2 содержит номер записи, который вы хотите найти
• «Signon» — это строка «Активность», которую вы ищете, и она находится в столбце D.
• "To" is the Column you're searching for.
  • The column titles in 3:3 stay the same throughout your sheet
• Каждая «запись» имеет 5 строк в высоту и 4 столбца в ширину.

Отсюда вы сможете расшифровать формулу и скорректировать ее по мере необходимости. ВПР/ГПР довольно ограничены... Обычно лучше потратить немного больше времени, чтобы использовать ИНДЕКС/ПОИСКПОЗ для поиска данных в другом месте вашего листа.

Надеюсь это поможет!

Более серьезная проблема заключается в том, что вы используете функции поиска, такие как MATCH INDEX, VLOOKUP, с отчетом, который не имеет удобной табличной формы данных.

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

Суть в том, что вам нужен какой-то способ пометить новые «записи» в необработанном отчете. Я предположил, что единственное время, когда «От» появляется в столбце A, — это указание новой записи, но вам может понадобиться другая формула, чтобы получить идентификатор для столбца F.

Тогда ваша формула станет простой =VLOOKUP("1234569Signon",$G:$H,2,false)

Итак, вы хотите что-то вроде =sign_off_time(1234569), которое возвращает 9:14? Это довольно легко. Но точная реализация будет сильно зависеть от того, где вы хотите считывать эти данные, и от того, на что похожа остальная часть вашей структуры листа.

По сути, фундаментальная проблема, с которой, я думаю, вы столкнулись, заключается в том, что VLOOKUP() нужно, чтобы индекс находился в первом столбце массива поиска, а затем можно найти значения только справа от индекса. Решение состоит в том, чтобы использовать комбинацию INDEX() и MATCH() вместо VLOOKUP(). Вот хороший отчет о том, как использовать ИНДЕКС/ПОИСКПОЗ и сравнение это с функцией ВПР.

РЕДАКТИРОВАТЬ: Хорошо, вы готовы? Вот так... если Sheet1 выглядит так:

1234569 |John   Smith   

From    To  Dur.    Activity
9:00    9:01    0:01    Open
8:55    10:15   1:20    Activity3
6:55    8:19    1:24    Activity4
9:01    9:14    0:13    Signon

4657915 Jane    Doe 

From    To  Dur.    Activity
8:55    10:15   1:20    Open
6:55    16:33   9:38    Activity4
11:55   15:42   3:47    Signon

8461224 Bob Dulluth 
(etc)

Тогда это будет работать в Sheet2 (или где угодно). Он находит строку идентификатора поиска (например, 1234569), затем находит первое вхождение «Signon» ниже этого (независимо от того, насколько далеко ниже), затем ищет второй столбец из этой строки (время выхода). Эта функция будет нарушена, если в записи пользователя нет действия "вход в систему", он найдет "вход" от следующего человека.

=INDEX(Sheet1!C2,MATCH("Signon",INDIRECT("Sheet1!R"&TEXT(MATCH(Sheet2!RC1,Sheet1!C1,0),"@")&"C4:R"&TEXT(ROWS(C3),"@")&"C4",FALSE),0)+MATCH(Sheet2!RC1,Sheet1!C1,0)-1)

Ссылка Sheet2!RC1 в формуле — это идентификатор поиска, поэтому при необходимости измените его.