Using Index/Match (or Something Else) to Return Row(s) of Data

[jbennett01]

My boss wanted me to create a search tool to assist our staff in locating relevant instructions, directives, etc. Below is the table I created wherein the user will enter one or more criteria to match. Based upon the criterion provided, Excel goes to the data source finds all the matching rows and outputs the results below the search table. I originally did this by writing a macro and using filters. Worked great and he was very impressed.

However, the search tool will reside on our intranet which uses the web-based Excel which does not support macros. To work, the user will need to download the tool to his or her computer. While functional, this has the obvious drawback of requiring the user to constantly, or frequently, download the current version. My boss is certain we can achieve the same result using Index and Match. I am not familiar with those functions so began researching them today. Sadly, the more I learn, the less I think they will work for this project. So I thought would reach out to experts.

The data source has these same 8 columns plus another for the source document designation and we need to return all 9 columns each time and, if there is more than 1 row which matches the input criteria, we need them all. Lastly, the source document column in the dataset is a link to the source document so the user can review the source document if needed. It would be very useful is that link could be preserved in the search results, as the macro does. The criteria are entered in column B and the dataset is stored in another worksheet called Directives with a current range of A2:I381 (row 1 is a header row).

Client(s)
Line of Business
Area
Category
Synopsis
Active	Yes
Start Date
End Date

 

[Kaper]

If you think about using this on web-based excel then almost ceratainly you can use Filter function.
The good point about it is that it's easy to return multiple results with it, while INDEX/MATCH combo without additional tricks would be rather "first available result" solution.

The point to start trials (assuming the table shown is in some other than directives sheet in A1:B8 cells) could be:

=IF(COUNTA(B1:B8)=0,"Please enter at least one search criterium",FILTER(Directives!A2:I381,IF(B1="",1,Directives!A2:A381=B1)*IF(B2="",1,Directives!B2:B381=B2)*IF(B3="",1,Directives!C2:C381=B3)*IF(B4="",1,Directives!D2:D381=B4)*IF(B5="",1,Directives!E2:E381=B5)*IF(B6="",1,Directives!F2:F381=B6)*IF(B7="",1,Directives!G2:G381=B7)*IF(B8="",1,Directives!H2:H381=B8),"No matching records found"))

In this version the link from column I is not working as link but is just listed

 

[AhoyNC]

This link on using the FILTER function on the web Excel may be of help.

 

[jbennett01]

If you think about using this on web-based excel then almost ceratainly you can use Filter function.
The good point about it is that it's easy to return multiple results with it, while INDEX/MATCH combo without additional tricks would be rather "first available result" solution.

The point to start trials (assuming the table shown is in some other than directives sheet in A1:B8 cells) could be:

=IF(COUNTA(B1:B8)=0,"Please enter at least one search criterium",FILTER(Directives!A2:I381,IF(B1="",1,Directives!A2:A381=B1)*IF(B2="",1,Directives!B2:B381=B2)*IF(B3="",1,Directives!C2:C381=B3)*IF(B4="",1,Directives!D2:D381=B4)*IF(B5="",1,Directives!E2:E381=B5)*IF(B6="",1,Directives!F2:F381=B6)*IF(B7="",1,Directives!G2:G381=B7)*IF(B8="",1,Directives!H2:H381=B8),"No matching records found"))

In this version the link from column I is not working as link but is just listed

Thank you. I will try this.

 

[jbennett01]

This link on using the FILTER function on the web Excel may be of help.

Great video. I will try this too.