Multiple Criteria Multiple Rows output in Excel using vlookup or index?

[tomjacobmartin]

I have a peculiar doubt...Is it possible to search multiple rows in an excel sheet that satisfies multiple criteria.

For Example, in the following table(First row is the header); If i have to search for data matching two criterias, Name= "Tom" AND Car= "AUDI" and get all the Service details for this combination(In this case, Routine Service, Windshield) in separate rows.

The forum has lot of solutions for multiple search criteria to give a single row, or a single vlookup with multiple rows as ouput, however im not getting Multi Criteria Multiple rows vlookup/Index case.

Can someone help me in this?

TIA!!![TABLE="width: 500"]
<tbody>[TR]
[TD]Name
[/TD]
[TD]Car
[/TD]
[TD]Service Details
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]AUDI
[/TD]
[TD]Routine Service
[/TD]
[/TR]
[TR]
[TD]Jon
[/TD]
[TD]BMW
[/TD]
[TD]Headlamp
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]Mercedez
[/TD]
[TD]Gear oil
[/TD]
[/TR]
[TR]
[TD]Sam
[/TD]
[TD]Mercedez
[/TD]
[TD]Tail Lamp
[/TD]
[/TR]
[TR]
[TD]Tom
[/TD]
[TD]AUDI
[/TD]
[TD]Windshield
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

Sheet1, A:C, houses the sample...

[TABLE="width: 324"]
<COLGROUP><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2702" width=76><COL style="WIDTH: 121pt; mso-width-source: userset; mso-width-alt: 5745" width=162><COL style="WIDTH: 146pt; mso-width-source: userset; mso-width-alt: 6940" width=195><TBODY>[TR]
[TD="class: xl63, width: 76, bgcolor: white"]Name[/TD]
[TD="class: xl63, width: 162, bgcolor: white"]Car[/TD]
[TD="class: xl63, width: 195, bgcolor: white"]Service Details[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 76, bgcolor: white"]Tom[/TD]
[TD="class: xl63, width: 162, bgcolor: white"]AUDI[/TD]
[TD="class: xl63, width: 195, bgcolor: white"]Routine Service[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 76, bgcolor: white"]Jon[/TD]
[TD="class: xl63, width: 162, bgcolor: white"]BMW[/TD]
[TD="class: xl63, width: 195, bgcolor: white"]Headlamp[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 76, bgcolor: white"]Tom[/TD]
[TD="class: xl63, width: 162, bgcolor: white"]Mercedez[/TD]
[TD="class: xl63, width: 195, bgcolor: white"]Gear oil[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 76, bgcolor: white"]Sam[/TD]
[TD="class: xl63, width: 162, bgcolor: white"]Mercedez[/TD]
[TD="class: xl63, width: 195, bgcolor: white"]Tail Lamp[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 76, bgcolor: white"]Tom[/TD]
[TD="class: xl63, width: 162, bgcolor: white"]AUDI[/TD]
[TD="class: xl63, width: 195, bgcolor: white"]Windshield[/TD]
[/TR]
</TBODY>[/TABLE]

Sheet2,column A, the processing...

[TABLE="width: 113"]
<COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5376" width=151><TBODY>[TR]
[TD="class: xl65, width: 151, bgcolor: transparent"]Tom[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Audi[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Service Details[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Routine Service[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"]Windshield[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent"] [/TD]
[/TR]
</TBODY>[/TABLE]

A4, control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX(Sheet1!$C$2:$C$6,SMALL(IF(Sheet1!$A$2:$A$6=A$1,
  IF(Sheet1!$B$2:$B$6=A$2,ROW(Sheet1!$C$2:$C$6)-ROW(Sheet1!$C$2)+1)),
  ROWS(A$4:A4))),"")

 

[tomjacobmartin]

Dear Aladin,

That solves the problem. Thank you for the quick reply!!!

 

[Aladin Akyurek]

Dear Aladin,

That solves the problem. Thank you for the quick reply!!!

You're welcome.