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

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

<tbody>
</tbody>

2. Returned car sheet

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

<tbody>
</tbody>

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

Car names	Mechanic	Problem	Time repaired	Time returned
Honda	green<strike></strike>	Engine	06/26/15 1:07 PM<strike></strike>	07/07/15 10:00 AM<strike></strike><strike></strike>
Honda	green<strike></strike>	Engine<strike></strike>	06/26/15 1:07 PM<strike></strike>	07/07/15 10:00 AM<strike></strike><strike></strike><strike></strike>
Honda	green<strike></strike>	Engine<strike></strike>	06/26/15 1:07 PM<strike></strike>	07/07/15 10:00 AM<strike></strike><strike></strike><strike></strike>
Honda	green<strike></strike>	Engine<strike></strike>	06/26/15 1:07 PM<strike></strike>	07/07/15 10:00 AM<strike></strike><strike></strike><strike></strike>

<tbody>
</tbody>

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!