How to create an Index&Match array formula with conditional criteria(s)

[DoskoiPanda]

Hi,

Original question posted to your excel magic trick 358 - Excel Magic Trick 358: Part 1: Return Multiple Items From One Lookup Value for Table w Formula - YouTube

"This is really cool. I have used this array formula for retrieving data from some very large databases. One thing I'd like to know is how you would construct the formula to still return values when using either one, or both of the criterias. For example, if you want to look at week 2 but see all bets, regardless of W or L and vice versa, if you want to see all weeks that had a W."

"Basically, I'd like to make a formula that says, if Criteria 1 is chosen but Criteria 2 is blank, just show Criteria 1 Matches. If Criteria 1 is blank and Criteria 2 is chosen, show Criteria 2 Matches. And of course, if both Criteria are chosen, then show Matches that match both Criteria."

I would like to improve my current index array formula which I am using to pull physician records based on criteria such as the name of their practice and last name and basically by any other field.

Here is a sample data set. (all of the names are fictional)

[TABLE="width: 713"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Data Set 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Specialty[/TD]
[TD]Group practice name[/TD]
[/TR]
[TR]
[TD]Adams[/TD]
[TD]Aaron[/TD]
[TD]Family Medicine[/TD]
[TD]Adamsville Clinic[/TD]
[/TR]
[TR]
[TD]Anderson[/TD]
[TD]Bob[/TD]
[TD]Psychiatry[/TD]
[TD]Anderson County Clinic[/TD]
[/TR]
[TR]
[TD]Anderson[/TD]
[TD]Christine[/TD]
[TD]Ophthalmology[/TD]
[TD]Memorial Hospital of Anderson[/TD]
[/TR]
[TR]
[TD]Bailey[/TD]
[TD]Denise[/TD]
[TD]Allergy & Immunology[/TD]
[TD]Bailey Clinic[/TD]
[/TR]
[TR]
[TD]Cross[/TD]
[TD]Eric[/TD]
[TD]Allied Health -
Cardiology[/TD]
[TD]Cross Cardiology & Associates[/TD]
[/TR]
[TR]
[TD]Davis[/TD]
[TD]Frank[/TD]
[TD]Family Medicine[/TD]
[TD]Davis County Family Medicine[/TD]
[/TR]
[TR]
[TD]Ellis[/TD]
[TD]George[/TD]
[TD]Geriatric[/TD]
[TD]Ellis County Senior Care[/TD]
[/TR]
[TR]
[TD]Ellis[/TD]
[TD]Hank[/TD]
[TD]Chiropractic[/TD]
[TD]St. Ellis Hospital[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Search For:[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Specialty[/TD]
[TD]Group practice name[/TD]
[/TR]
[TR]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[TD]xx[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Display Results:[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last[/TD]
[TD]First[/TD]
[TD]Specialty[/TD]
[TD]Group practice name[/TD]
[/TR]
[TR]
[TD]yy[/TD]
[TD]yy[/TD]
[TD]yy[/TD]
[TD]yy[/TD]
[/TR]
</tbody>[/TABLE]


I would like to be type in data to replace the "xx"s and have the array formulas where the "yy"s are so that the table will automatically updated with relevant data. For example, if I type "Ellis" under Last where the "xx" is, the resulting Display results for "yy" will show both doctors with the last name Ellis. Then, if I type in "Chiropractic" under Specialty "xx", it will only show the Doctor named Ellis, who is also a Chiropractic.

In a perfect world, I want to ultimately be able to search and specify my search with any of the fields.

I'd like to thank you for your interest in my problem and hope that I don't waste too much of your time on this.

Best regards

 

[circledchicken]

Hi and welcome to the forum,

Is there a specific reason you need to have a formula solution that does this? The built-in auto-filter feature allows you to do almost exactly this using the appropriate filters (and it's much easier!).

 

[DoskoiPanda]

Hi and thanks,

That is true, the filters do accomplish this task.
I guess the main reason I wanted to do this besides the fact that I think using complicated formulas that may have a slightly superfluous advantage is cool, is largely because I'm organizing several thousand records that I want to keep on a separate, protected sheet so that people that want to use my database to look up a doctor, can do so without tampering with the raw data. Protecting cells is probably another option that can accomplish that. So what I envision is that on one sheet I will have 'search bars' for the user to use, and on the second sheet will be the raw data which I will hide+protect.

Also, when conducting multiple searches with several conditions, it becomes tedious clicking on the filters/unfiltering/searching/refiltering. I have ran into the problem that making a formula that satisfies my demands is also equally if not more tedious. I was just hoping some brilliant person had a perceptive solution. Right now I'm using a semi functional index/match array formula nested in between a bunch of iferrors.

 

[circledchicken]

Ok - I'm sure someone can help you with formulae to achieve this but I'm not sure its a good idea given your large dataset. Ideally I think this sort of task might be suited to Microsoft Access for example, but there are a few other options in Excel you might want to try, including:

• PivotTables - these will give you the filter functionality but the raw data can stay in a separate area.
• Parameterized queries using MS Query - this will allow you to have your criteria typed in cells and conduct multiple searches with several conditions as per your original post and would also allow you to keep your raw data in a separate source.

For more on each of these options the following resources should help:

• PivotTables:
  • Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
  • Excel Pivot Table -- Dynamic Data Source
  • Overview of PivotTable and PivotChart reports - Excel - Office.com
• Queries:
  • Build Queries
  • Exercise 2: Creating and using Parameterized Queries in Excel
  • Importing Data with Parameters (ODBC) - Nick Hodge's Excel Blog - Excel User Group
  • XL2000: Parameter Query Is Case-Sensitive