VLOOKUP with 2 criterias

[LaurenHancy]

Hi All,

I am having a real problem with return the time of a cell using 2 criterias:

This is the data table:

A B C D

1 02/01/19 John 08:16 06:40
2 03/01/19 Bob 08:20 05:30
3 04/05/19 John 09:45 06:44
4 04/03/19 David 08:20 07:55

I am wanting to do an index search as per the following into each date collumn I have. As the referance collumns are not underneath each other, my VLOOKup doesnt work, it just returns #N/A.

=VLOOKUP(A6&D5, RAW_UserAvSum_DATA, 3,FALSE)

A6 = Name
D5 = Date

I want to return Collumn C, first time record.

Thank you for helping me x

 

[steve the fish]

Re: Please help me, I'm stuck - VLOOKUP with 2 criterias

That means the result is false so those two cells are not equal even if they appear to be. Is the sumproduct returning #N/A?

 

[LaurenHancy]

Re: Please help me, I'm stuck - VLOOKUP with 2 criterias

results from post #2 & #3's formula

	A	B	C	D
John
Bob
John
David
John

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]02/05/2019[/TD]

[TD="align: right"]08:16[/TD]
[TD="align: right"]06:40[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]03/01/2019[/TD]

[TD="align: right"]08:20[/TD]
[TD="align: right"]05:30[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]04/05/2019[/TD]

[TD="align: right"]09:45[/TD]
[TD="align: right"]06:44[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]04/03/2019[/TD]

[TD="align: right"]08:20[/TD]
[TD="align: right"]07:55[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]04/05/2019[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]09:45[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=E2EFDA]#E2EFDA[/URL] , align: right"]09:45[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet9​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C6[/TH]
[TD="align: left"]=SUMPRODUCT(--($A$1:$A$4=D5),--($B$1:$B$4=A6),$C$1:$C$4)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]=INDEX(C1:C4,MATCH(1,INDEX((A1:A4=D5)*(B1:B4=A6),0),0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

I can get this to work on a blank spreadsheet but it isnt working on my main spreadsheet, would it be because the data I am using is imported using =TEXT(**,"DD/MM/YYY")?

 

[AlanY]

Re: Please help me, I'm stuck - VLOOKUP with 2 criterias

instead of import using =TEXT(**,"DD/MM/YYY"), try =TEXT(**,"DD/MM/YYY")+0

 

[LaurenHancy]

Re: Please help me, I'm stuck - VLOOKUP with 2 criterias

It has worked! Thank you very much

=TEXT(**,"DD/MM/YYY")+0

 

[AlanY]

Re: Please help me, I'm stuck - VLOOKUP with 2 criterias

this is the modified version

Book1
	A	B	C	D
1	02/05/2019	John	08:16	06:40
2	03/01/2019	Bob	08:20	05:30
3	04/05/2019	John	09:45	06:44
4	04/03/2019	David	08:20	07:55
5				04/05/2019
6	John		09:45
7
8			09:45
Sheet9

Cell Formulas
Range	Formula
C6	=SUMPRODUCT(--(TEXT($A$1:$A$4,"dd/mm/yyyy")+0=D5),--($B$1:$B$4=A6),$C$1:$C$4)
C8	=INDEX(C1:C4,MATCH(1,INDEX((TEXT(A1:A4,"dd/mm/yyyy")+0=D5)*(B1:B4=A6),0),0))

 

[steve the fish]

Re: Please help me, I'm stuck - VLOOKUP with 2 criterias

Based on your opening post:

I want to return Collumn C, first time record.

do not use the SUMPRODUCT. It will add the times together if multiple entries exist.

 

[AlanY]

Re: Please help me, I'm stuck - VLOOKUP with 2 criterias

It has worked! Thank you very much

=TEXT(**,"DD/MM/YYY")+0

you're welcome.
as Steve suggest the index/match option is more robust

 

[Owain_b]

Re: Please help me, I'm stuck - VLOOKUP with 2 criterias

Hi Lauren,

It looks like you have your A6 and D5 the wrong way around. D5 should be in the first part of the formula I think.

Owain