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?

 

[sbawnh]

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.

Hey scott, in terms of speed,You're saying IFERROR is worse than using two vlookups like: IF(ISNA(vlookup()),"",vlookup())

 

[Scott T]

Since ISNA would have to run the VLOOKUP to see if it is NA just like IFERROR would run it to see if it resulted in an error there should not be much difference. But if you can avoid a more complicated formula by using something simple then you will get more speed.

For example in the original formula I posted

[COLOR=#333333]=IF([/COLOR][COLOR=Blue]COLUMNS([COLOR=Red]$D2:D2[/COLOR])>COUNTIF([COLOR=Red]$A$2:$A$4,$A2[/COLOR]),"",TEXT([COLOR=Red]INDEX([COLOR=Green]$B$2:$B$4,SMALL([COLOR=Purple]IF([COLOR=Teal]$A2=$A$2:$A$4,ROW([COLOR=#FF00FF]$B$2:$B$4[/COLOR])-ROW([COLOR=#FF00FF]$B$2[/COLOR])+1[/COLOR]),COLUMNS([COLOR=Teal]$D2:D2[/COLOR])[/COLOR])[/COLOR]),"mm/dd/yyyy"[/COLOR])&" "&TEXT([COLOR=Red]INDEX([COLOR=Green]$C$2:$C$4,SMALL([COLOR=Purple]IF([COLOR=Teal]$A2=$A$2:$A$4,ROW([COLOR=#FF00FF]$B$2:$B$4[/COLOR])-ROW([COLOR=#FF00FF]$B$2[/COLOR])+1[/COLOR]),COLUMNS([COLOR=Teal]$D2:D2[/COLOR])[/COLOR])[/COLOR]),"H:mm:ss"[/COLOR])[/COLOR][COLOR=#333333])[/COLOR]

If you used IFERROR then when the cell calculated this would have to be calculated every time even if the cells should return blank

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

By using a different test to see if the cell should be blank then only this has to be calculated and the rest of the formula only calculates when need.

[COLOR=blue]COLUMNS([/COLOR][COLOR=Red]$D2:D2[/COLOR][COLOR=blue])>COUNTIF([/COLOR][COLOR=Red]$A$2:$A$4,$A2[/COLOR][COLOR=blue])[/COLOR]