Geek asks and answers

Vlookup and get the value min (date)

TOP Table is the input, and the bottom table is a preview of the required output.

For each identifier I need to find an early time and time. I also need other information from other columns (see the figure below).

My current solution: In cell E2, =A2
 move cell E3 down=IF(E2<>A3,IF(E1=A3,"",A3),"")

In cell F2, drag =IF(E2<>"",MIN(IF($A$2:$A$14=E2,$C$2:$C$14)),"")Ctrl + Shift + Enter

enter image description here

+5
source share
5 answers

Another option without intermediate calculations:

  • E2 , - , 14, E2:E14: =IFERROR(INDEX($A$2:$A$14,SMALL(IF(MATCH($A$2:$A$14,$A$2:$A$14,0)=ROW(INDIRECT("1:"&ROWS($A$2:$A$14))),MATCH($A$2:$A$14,$A$2:$A$14,0),""),ROW(INDIRECT("1:"&ROWS($A$2:$A$14))))),"") CTRL + SHIFT + ENTER ENTER - Multicell ARRAY {} ( NOT !).
  • F2 (ID2): =IF(E2="","",SUMPRODUCT(--(E2=$A$2:$A$14),--(G2=$C$2:$C$14),$B$2:$B$14)) - .
  • G2 (Min Date): =IF(E2="","",MIN(IF(E2=$A$2:$A$14,$C$2:$C$14,2^100))) CTRL + SHIFT + ENTER ENTER - ARRAY, {} ( !).
  • H2 (InCh): =IF(E2="","",INDEX($D$2:$D$14,SUMPRODUCT(--(E2=$A$2:$A$14),--(F2=$B$2:$B$14),--(G2=$C$2:$C$14),ROW(INDIRECT("1:"&ROWS($D$2:$D$14)))))) - .

:

  • , ID, , OFFSET.
  • ID2 - 1...3.
  • Min Date , Date.
  • - , - OP)

: https://www.dropbox.com/s/d2098updfh8djnf/MinDateIDs.xlsx

+8

... , , . . , , . .

:

Screenshot of XLS with helper columns

:

Screenshot of the newly inserted columns

F . . I, F2,

=INDEX($A$2:$A$14, MATCH(MIN(IF(COUNTIF($F$1:F1,$A$2:$A$14)=0, 1, MAX((COUNTIF($A$2:$A$14, "<"&$A$2:$A$14)+1)*2))*(COUNTIF($A$2:$A$14, "<"&$A$2:$A$14)+1)), COUNTIF($A$2:$A$14, "<"&$A$2:$A$14)+1, 0))

Ctrl - Shift - Enter . F. G H ID. , , :-). :

=MATCH(2,1/FREQUENCY($F2,$A$2:$A$14))

G2

=FREQUENCY($A$2:$A$14,$F2)

H2. , . , I - . . J MATCH, .

=MIN(OFFSET($C$2:$C$14,$G2-1,0,1+$H2-$G2,1))

I2

=$G2-1+MATCH(2,1/FREQUENCY(MIN(OFFSET($C$2:$C$14,$G2-1,0,1+$H2-$G2,1)), OFFSET($C$2:$C$14,$G2-1,0,1+$H2-$G2,1))

J2. , L, M N

=INDEX(B$2:B$14,$J2)

L2, , .

, . ... , .

+2

D G, , C , VLOOKUP, G2

=VLOOKUP(F2,C$2:D$14,2,0)

+1

. ID, Date ( ). ID, , ID, , Date ID. , , ID, ( , ).

... .

0

, , ! Minimum field, =GETPIVOTDATA(), ! , !

0
source

More articles:


All Articles