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!!!

Name	Car	Service Details
Tom	AUDI	Routine Service
Jon	BMW	Headlamp
Tom	Mercedez	Gear oil
Sam	Mercedez	Tail Lamp
Tom	AUDI	Windshield

<tbody>
</tbody>

 

[Aladin Akyurek]

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

Name	Car	Service Details
Tom	AUDI	Routine Service
Jon	BMW	Headlamp
Tom	Mercedez	Gear oil
Sam	Mercedez	Tail Lamp
Tom	AUDI	Windshield

<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>
</TBODY>

Sheet2,column A, the processing...

Tom

Audi

Service Details

Routine Service

Windshield

<COLGROUP><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5376" width=151><TBODY>
</TBODY>

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.