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
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet9​

Worksheet Formulas Cell Formula C6 =SUMPRODUCT(--($A$1:$A$4=D5),--($B$1:$B$4=A6),$C$1:$C$4) C8 =INDEX(C1:C4,MATCH(1,INDEX((A1:A4=D5)*(B1:B4=A6),0),0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

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