Search a table and return rows based on criteria of 2 cells

slugwisper

New Member
Joined
Aug 20, 2018
Messages
3
Hello!

I'm looking to search a table like the the one below. In a separate sheet in the workbook, I'd like to have the "Subject" as multiple columns and list underneath it all of the titles that match the subject and have the language in english. I tried working a combination of index and match but i was getting entire rows of data instead of just the title. I would greatly appreciate any help!

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style><style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
TitleLanguageSubject
Idiomsenglishlanguage-arts
Refranesspanishlanguage-arts
Name That Character Traitenglishlanguage-arts
Nombra el rasgo del personajespanishlanguage-arts
Telling Storiesenglishlanguage-arts
Contar una historiaspanishlanguage-arts
Todo sobre la Energíaspanishphysical-science-and-engineering
All About Energyenglishphysical-science-and-engineering
Build an Epic Sandcastle!englishphysical-science-and-engineering
Construye un castillo de arena épicospanishphysical-science-and-engineering
Just Press Print!englishphysical-science-and-engineering
Solo presiona imprimirspanishphysical-science-and-engineering
Making Better Bubblesenglishphysical-science-and-engineering
Címo hacer mejores burbujasspanishphysical-science-and-engineering
Making Crayonsenglishphysical-science-and-engineering

<colgroup><col style="width: 100px"><col width="100"><col width="182"></colgroup><tbody>
</tbody>


Want to return:

<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>
physical-science-and-engineeringlanguage-arts
All About EnergyIdioms
Build an Epic Sandcastle!Name That Character Trait
Just Press Print!Telling Stories

<colgroup><col style="width: 186px"><col width="145"></colgroup><tbody>
</tbody>
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
41,024
Office Version
365
Platform
Windows
How about
In B1 copied right
=IFERROR(INDEX(Sheet1!$C$2:$C$16,MATCH(0,INDEX(COUNTIF($A$1:A$1,Sheet1!$C$2:$C$16),0),0)),"")
and in B2 copied down & across
=IFERROR(INDEX(Sheet1!$A$2:$A$16,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$16)-ROW(Sheet1!$A$2)+1)/((Sheet1!$B$2:$B$16="english")*(Sheet1!$C$2:$C$16=B$1)),ROWS($A$1:$A1))),"")
 

Watch MrExcel Video

Forum statistics

Threads
1,099,684
Messages
5,470,121
Members
406,681
Latest member
sachinmasurkar

This Week's Hot Topics

Top