Return a second value as well, from another column when doing VLOOKUP

[sbawnh]

I'm wondering if this is easy and i'm just thinking too hard at this problem.
I have A:C, I need the formula to return D:H like this
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]04/05/2019[/TD]
[TD]14:28:00[/TD]
[TD]04/05/2019-14:28:00[/TD]
[TD]04/07/2019-13:12:00[/TD]
[TD]04/07/2019-15:57:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]04/07/2019[/TD]
[TD]13:12:00[/TD]
[TD]04/05/2019-14:28:00[/TD]
[TD]04/07/2019-13:12:00[/TD]
[TD]04/07/2019-15:57:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dog[/TD]
[TD]04/07/2019[/TD]
[TD]15:57:00[/TD]
[TD]04/05/2019-14:28:00[/TD]
[TD]04/07/2019-13:12:00[/TD]
[TD]04/07/2019-15:57:00[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

This is the formula for the dates portion found in D:G ( Sort and list each occurrence of Date, matches criteria of Name)
"AboveFormula"={=SMALL(IF(($A2=$A$2:$A$4),$B$2:$B$4),COLUMNS($D$2:D2))}

Then I want to bring in the exact time too.
If i did AboveFormula&"-"&Vlookup(AboveFormula,C2:c4,3,0) it would duplicate the 13:12:00 time from Column C into Column F

Extra Details
I found out that Vlookup(AboveFormula,c2:c4,{2,3},0) returns ={43560,"14:28:00"} That's perfect, But I can't find how to display the full answer.

Any ideas?

 

[CyrusTheVirus]

Probably the easiest way would be to add a helper column to the left of the data set (see below). Then do the two below formulas

A2 (copied down):

=COUNTIFS($B$2:B2,B2)&B2

E2 (copied over and down):

=IFERROR(TEXT(VLOOKUP(COLUMNS($H$2:H2)&$B2,$A$2:$C$4,3,0),"mm/dd/yyyy")&"-"&TEXT(VLOOKUP(COLUMNS($H$2:H2)&$B2,$A$2:$D$4,4,0),"hh:mm:ss"),"")

[TABLE="width: 582"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Helper[/TD]
[TD]Name[/TD]
[TD]Date[/TD]
[TD]Time[/TD]
[TD]Result 1[/TD]
[TD]Result 2[/TD]
[TD]Result 3[/TD]
[/TR]
[TR]
[TD]1Dog[/TD]
[TD]Dog[/TD]
[TD="align: right"]4/5/2019[/TD]
[TD="align: right"]2:28:00 PM[/TD]
[TD]04/05/2019-14:28:00[/TD]
[TD]04/07/2019-13:12:00[/TD]
[TD]04/07/2019-15:57:00[/TD]
[/TR]
[TR]
[TD]2Dog[/TD]
[TD]Dog[/TD]
[TD="align: right"]4/7/2019[/TD]
[TD="align: right"]1:12:00 PM[/TD]
[TD]04/05/2019-14:28:00[/TD]
[TD]04/07/2019-13:12:00[/TD]
[TD]04/07/2019-15:57:00[/TD]
[/TR]
[TR]
[TD]3Dog[/TD]
[TD]Dog[/TD]
[TD="align: right"]4/7/2019[/TD]
[TD="align: right"]3:57:00 PM[/TD]
[TD]04/05/2019-14:28:00[/TD]
[TD]04/07/2019-13:12:00[/TD]
[TD]04/07/2019-15:57:00[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Book1
	A	B	C	D	E	F	G	H
1	Name	Date	Time	D	E	F	G	H
2	Dog	4/5/2019	14:28:00	04/05/2019 14:28:00	04/07/2019 13:12:00	04/07/2019 15:57:00
3	Dog	4/7/2019	13:12:00	04/05/2019 14:28:00	04/07/2019 13:12:00	04/07/2019 15:57:00
4	Dog	4/7/2019	15:57:00	04/05/2019 14:28:00	04/07/2019 13:12:00	04/07/2019 15:57:00
Sheet2

Cell Formulas
Range	Formula
D2	{=IF(COLUMNS($D2:D2)>COUNTIF($A$2:$A$4,$A2),"",TEXT(INDEX($B$2:$B$4,SMALL(IF($A2=$A$2:$A$4,ROW($B$2:$B$4)-ROW($B$2)+1),COLUMNS($D2:D2))),"mm/dd/yyyy")&" "&TEXT(INDEX($C$2:$C$4,SMALL(IF($A2=$A$2:$A$4,ROW($B$2:$B$4)-ROW($B$2)+1),COLUMNS($D2:D2))),"H:mm:ss"))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy the formula across and down.

 

[sbawnh]

Cyrus, That's amazing! I used your helper column and your 2nd Vlookup with the original dates formula. Works wonderfully!

THANK YOU.

=IF($B2="","",IFERROR(TEXT(SMALL(IF(($B2=$B$2:$B$999),$F$2:$F$999),COLUMNS($I$2:I2)),"MM/DD/YYYY")&"-"&TEXT(VLOOKUP(COLUMNS($I$2:I2)&$B2,$A$2:$G$999,7,0),"hh:mm:ss"),""))

 

[sbawnh]

Scott, thats INTERESTING! Thank you!

Love the use without helper columns!!

Very similar to my formula, except i've usually thought these two formulas were equal and have preferred the elegance of shorter formulas.

=IFERROR(TEXT(SMALL(IF($B2=$B$2:$B$500,$F$2:$F$500),COLUMNS($I2:I2)),"mm/dd/yyyy")&"-"&TEXT([B]SMALL(IF($B2=$B$2:$B$500,$F$2:$F$500),COLUMNS($I2:I2))[/B],"H:mm:ss"),"")

VS

=IFERROR(TEXT(SMALL(IF($B2=$B$2:$B$500,$F$2:$F$500),COLUMNS($I2:I2)),"mm/dd/yyyy")&"-"&TEXT([B]INDEX($G$2:$G$500,SMALL(IF($B2=$B$2:$B$500,ROW($F$2:$F$500)-ROW($F$2)+1),COLUMNS($I2:I2)))[/B],"H:mm:ss"),"")

Enjoy your weekend, thank you both very much.

 

[sbawnh]

Scott, I noticed something. The dates aren't being sorted Old - New with the use of the SMALL function.

Changing the first Half to this fixes the dates, but the Times aren't matched up, which I had a problem with too. Love the idea to use no helper column. I am down for that if you think of anything!!

=IFERROR(TEXT(SMALL(IF($B2=$B$2:$B$500,$F$2:$F$500),COLUMNS($I2:I2)),"mm/dd/yyyy")&" "&TEXT(INDEX($G$2:$G$500,SMALL(IF($B2=$B$2:$B$500,ROW($F$2:$F$500)-ROW($F$2)+1),COLUMNS($I2:I2))),"H:mm:ss"),"")

 

[CyrusTheVirus]

Cool. Glad it helped.

 

[Scott T]

Try

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

=IF(COLUMNS($D2:D2)>COUNTIF($A$2:$A$6,$A2),"",SMALL(IF($A$2:$A$6=$A2,$B$2:$B$6+$C$2:$C$6),COLUMNS($D2:D2)))

Be carful when using IFERROR as it will hide any real errors and IFERROR always has to run the value to see if there is an error. IF the sheet is large and the formula in the value argument is complex it can slow down calculations.

 

[sbawnh]

Appreciate the heads up on the IFERROR. I use it because it eliminates a lookup when searching for #N/A's instead of using ISNA, but it has caused false blanks before on #ref's that showed up. I should stop using it so freely.

Your formula is only returning the dates, sort of like i'm only pressing =B2 and dragging down. You can test by typing =B2+C2 as (Date + Time)

 

[Scott T]

You should get the date and time. You do have to format the cell appropriately.