Make Index and Match Accurate

[romeo16]

I have two sheets , one is for repaired car and another one is for returned car so I am trying to create an index formula that returns the returned car's brand name , Mechanic name and the problem but the information isn't accurate

Note: there is only one car per brand name , which means if you see a repeated brand name it means it has been returned and repaired again with a different mechanic

1. repaired car sheet

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]CAR NAMES[/TD]
[TD]MECHANIC[/TD]
[TD]DATE/TIME[/TD]
[/TR]
[TR]
[TD]Chevy[/TD]
[TD]Arnold[/TD]
[TD]06/10/15 07:00 AM[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]maroon[/TD]
[TD]06/25/15 09:00 AM[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]green[/TD]
[TD]06/26/15 1:07 PM[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]Bruce[/TD]
[TD]07/07/15 10:00 AM[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]eddy[/TD]
[TD]07/09/15 08:00 AM[/TD]
[/TR]
[TR]
[TD]Chevy[/TD]
[TD]Mike[/TD]
[TD]07/09/15 08:30 AM[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]Andy[/TD]
[TD]07/25/15 10:00 AM[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]William[/TD]
[TD]08/29/15 5:28 PM[/TD]
[/TR]
[TR]
[TD]Chevy[/TD]
[TD]james[/TD]
[TD]09/17/15 10:00 AM[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]julie[/TD]
[TD]09/20/15 09:40 AM[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]April[/TD]
[TD]10/20/15 09:09 AM<strike></strike>[/TD]
[/TR]
[TR]
[TD]Chevy[/TD]
[TD]Charlie[/TD]
[TD]10/25/15 06:05 AM[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Andy_2[/TD]
[TD]10/30/15 08:10 AM[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Mike_2[/TD]
[TD]06/01/16 05:00 AM[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]Santos[/TD]
[TD]07/02/16 06:24 AM[/TD]
[/TR]
</tbody>[/TABLE]


2. Returned car sheet

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]CAR NAMES[/TD]
[TD]PROBLEM[/TD]
[TD]DATE/TIME[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Engine[/TD]
[TD]07/07/15 10:00 AM<strike></strike>[/TD]
[/TR]
[TR]
[TD]Chevy[/TD]
[TD]Security_lock[/TD]
[TD]07/05/15 9:00 PM[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]Window[/TD]
[TD]07/24/15 1:07 PM[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Front door[/TD]
[TD]08/27/15 6:18 AM[/TD]
[/TR]
[TR]
[TD]Chevy[/TD]
[TD]Mirror[/TD]
[TD]09/16/15 11:00 AM<strike></strike>[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]Paint[/TD]
[TD]09/18/15 4:30 PM[/TD]
[/TR]
[TR]
[TD]BMW[/TD]
[TD]HeadLight[/TD]
[TD]10/17/15 5:50 AM[/TD]
[/TR]
[TR]
[TD]Chevy[/TD]
[TD]Trunk[/TD]
[TD]10/23/15 8:00 AM[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Seats[/TD]
[TD]10/27/15 4:20 AM[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]Trunk[/TD]
[TD]05/31/16 5:20 AM[/TD]
[/TR]
[TR]
[TD]Toyota[/TD]
[TD]Windshield[/TD]
[TD]07/01/16 07:18 AM<strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]

My formula is only picking the first match's information
ex: for Honda is returning This:

[TABLE="class: grid, width: 600, align: center"]
<tbody>[TR]
[TD]Car names[/TD]
[TD]Mechanic[/TD]
[TD]Problem[/TD]
[TD]Time repaired[/TD]
[TD]Time returned[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]green<strike></strike>[/TD]
[TD]Engine[/TD]
[TD]06/26/15 1:07 PM<strike></strike>[/TD]
[TD]07/07/15 10:00 AM<strike></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]green<strike></strike>[/TD]
[TD]Engine<strike></strike>[/TD]
[TD]06/26/15 1:07 PM<strike></strike>[/TD]
[TD]07/07/15 10:00 AM<strike></strike><strike></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]green<strike></strike>[/TD]
[TD]Engine<strike></strike>[/TD]
[TD]06/26/15 1:07 PM<strike></strike>[/TD]
[TD]07/07/15 10:00 AM<strike></strike><strike></strike><strike></strike>[/TD]
[/TR]
[TR]
[TD]Honda[/TD]
[TD]green<strike></strike>[/TD]
[TD]Engine<strike></strike>[/TD]
[TD]06/26/15 1:07 PM<strike></strike>[/TD]
[TD]07/07/15 10:00 AM<strike></strike><strike></strike><strike></strike>[/TD]
[/TR]
</tbody>[/TABLE]

I want to return from all Cars not for Honda only , Thank you in Advance!!

 

[mrCostas]

Use a helper column in both sheets (as in here) so that Honda1 corresponds to the first Honda, Honda2 to the second Honda,BMW1 to the first BMW etc. This will allow you to merge the two sheets together with a simple vlookup (assuming that the first Honda in sheet1 corresponds the the first Honda in sheet2 etc.).
Finally, just add a filter to your merged table by the car name you want and you should be getting the desired result.

 

[romeo16]

Use a helper column in both sheets (as in here) so that Honda1 corresponds to the first Honda, Honda2 to the second Honda,BMW1 to the first BMW etc. This will allow you to merge the two sheets together with a simple vlookup (assuming that the first Honda in sheet1 corresponds the the first Honda in sheet2 etc.).
Finally, just add a filter to your merged table by the car name you want and you should be getting the desired result.

Thanks for that but what if we have a huge data set?

 

[mrCostas]

You are welcome. Maybe you can try something like this ?

 

[romeo16]

You are welcome. Maybe you can try something like this ?

Thanks very much now it's working perfect.

 

[mrCostas]

Thanks very much now it's working perfect.

You are welcome!