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

Try like this:

=INDEX(C1:C4,MATCH(1,INDEX((A1:A4=D5)*(B1:B4=A6),0),0))

 

[AlanY]

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

not a vlookup() solution

Book1
	A	B	C	D
1	02/01/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
6	04/05/2019	John	09:45
Sheet9

Cell Formulas
Range	Formula
C6	=SUMPRODUCT(--($A$1:$A$4=A6),--($B$1:$B$4=B6),$C$1:$C$4)

 

[steve the fish]

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

not a vlookup() solution

	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/01/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"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]04/05/2019[/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=A6),--($B$1:$B$4=B6),$C$1:$C$4)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

That has a bit of a problem should there be two entries for the same person on the same date.

 

[Owain_b]

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

Hi,

A quick and easy way to achieve this would be to insert a column before column A and enter =CONCATENATE(D2,C2). This would be C2 and B2 in your table above but I have inserted a column. So that covers the what you are looking up.

Then change you lookup to: =VLOOKUP(A6&D5,RAW_UserAvSum_DATA, 5,FALSE)

That should do it.

Hope that helps

 

[AlanY]

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

That has a bit of a problem should there be two entries for the same person on the same date.

points taken, thanks

 

[LaurenHancy]

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

Hi All,

I have tried all of the above but it doesnt seem to work. I have tried concertrating 2 collumns then using Vlookup function, also tried Sum Product, but nothing seems to work all I get is #N/A.

 

[steve the fish]

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

Find the row where you expect the solution to be found. Then check that cell A6 equals the name and cell D5 equals the date of that same row.

eg.

=A6=B1

and

=D5=A1

because at least one must be false.

 

[LaurenHancy]

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

Hi Steve,

The result returns 00:00:00

I am using the following forular to be exact:

=SUMPRODUCT(--('RAW - UserAvSummary'!$B$8:$B$5000=A6),--('RAW - UserAvSummary'!$A$8:$A$5000=D5),'RAW - UserAvSummary'!$C$8:$C$5000)

A6 = Name
D5 = Date

I am not too sure what I am doing wrtong?

 

[AlanY]

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

results from post #2 & #3's formula

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(--($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))