Author: Oscar Cronquist Article last updated on June 04, 2020

Lookup with an unknown number of criteria new

This article demonstrates a formula that allows you to search a data set using any number of conditions, however, one condition per column. The search functionality matches the entire value in a cell, no wildcard matches.

It also demonstrates how to extract the records using the Advanced Filter. The Advanced Filter is a built-in Excel feature that allows you to do more complicated filtering than the regular Filter feature or an Excel Table can accomplish.

Rashid asks:I used your array formula with great success to find the search results from multiple criteria. However, my problem is modifying your formula. In the above example you have shown us, you have two criteria. And you distinguish the two criteria by using *.

My question is: what would you do if you don't know the predetermined number of criteria. So let's say the person searching only specifies security and not date. Or only date, and not security. Or maybe both. The problem is you don't know from beforehand.
How would you go about solving this problem?

Your help is greatly appreciated, thanks so much!!

Lookup using an unknown number of criteria

The animated image above shows different conditions being used and how the formula instantly returns records that match. It also shows that you can use multiple conditions to narrow down the results. The data is located on worksheet Sheet2.

Formula in cell B8:

=IFERROR(IF(COUNTA($B$4:$E$4)<>0, INDEX(Sheet2!$A$2:$D$21, SMALL(IF(COUNTA($B$4:$E$4)=(COUNTIF($B$4, Sheet2!$A$2:$A$21)+COUNTIF(Sheet1!$C$4, Sheet2!$B$2:$B$21)+COUNTIF(Sheet1!$D$4, Sheet2!$C$2:$C$21)+COUNTIF(Sheet1!$E$4, Sheet2!$D$2:$D$21)), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), ""), ROW(A1)), COLUMN(A1)), ""), "")

This formula requires you to enter it as an array formula if you have an older Excel version than an Excel 365 subscription, the steps to enter an array formula are below.

This formula does not spill values automatically if needed, you have to type the formula in a cell and then press enter. Copy the cell and paste to adjacent cells below and to the right as far as needed.

How to enter an array formula

  1. Select cell B8
  2. Type the above array formula
  3. Press and hold CTRL + SHIFT
  4. Press Enter
  5. Release all keys

If you got it right, there is now a { before the array formula and a } after the array formula.

How to copy array formula

  1. Copy cell B8
  2. Paste to C8:E8
  3. Copy cell range B8:E8
  4. Paste to cell range B9:E19

Explaining the array formula in cell B8

Lookup with an unknown number of criteria new dat set

The image above shows the data on worksheet Sheet2, it contains random names, countries and dates.

I recommend the "Evaluate Formula" feature if you want to see the formula calculations in greater detail.

Lookup with an unknown number of criteria new evaluate formula

  1. Select the cell containing the formula you want to learn.
  2. Go to tab "Formulas" on the ribbon.
  3. Click the "Evaluate Formula" button. A dialog box appears.
  4. Click the "Evaluate" button to move to next calculation step.
  5. Click "Close" to dismiss the dialog box.

Step 1 - Count the number of cells in each table column that meet the criteria and add the arrays

The COUNTIF function counts values based on a condition or criteria. There are four COUNTIF functions, as many as there are columns in the data set.

The returning arrays are added together, this means we apply OR logic to the result meaning if any of the values in the same position are one the result is one or more.

COUNTIF($B$4, Sheet2!$A$2:$A$21)+COUNTIF(Sheet1!$C$4, Sheet2!$B$2:$B$21)+COUNTIF(Sheet1!$D$4, Sheet2!$C$2:$C$21)+COUNTIF(Sheet1!$E$4, Sheet2!$D$2:$D$21)

becomes

{0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0})+{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}+{0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}+{0;0;1;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0}

and returns

{0;0;2;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0}

This means that row 3 (relative row) matches two conditions. Why is that? 2 is the third value in the array.

Step 2 - Count the number of criteria matching and compare with each value in the array above

The IF function has three arguments, the first one must be a logical expression. If the expression evaluates to TRUE then one thing happens (argument 2) and if FALSE another thing happens (argument 3).

IF(COUNTA($B$4:$E$4)=(COUNTIF($B$4, Sheet2!$A$2:$A$21)+COUNTIF(Sheet1!$C$4, Sheet2!$B$2:$B$21)+COUNTIF(Sheet1!$D$4, Sheet2!$C$2:$C$21)+COUNTIF(Sheet1!$E$4, Sheet2!$D$2:$D$21)), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), "")

becomes

IF(2={0;0;2;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0}, MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), "")

becomes

IF(2={0;0;2;0;0;0;0;0;0;0;0;0;0;0;1;0;0;0;0;0}, {1;2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20}, "")

becomes

IF({FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}, {1;2;3;4;5; 6;7;8;9;10; 11;12;13;14; 15;16;17;18;19;20}, "")

and returns

{"";"";3;""; "";"";"";"";""; "";"";"";"";"";""; "";"";"";"";""}

Step 3 - Find the k-th smallest row number

To be able to return a new value in a cell each I use the SMALL function to filter column numbers from smallest to largest.

SMALL(IF(COUNTA($B$4:$E$4)=(COUNTIF($B$4, Sheet2!$A$2:$A$21)+COUNTIF(Sheet1!$C$4, Sheet2!$B$2:$B$21)+COUNTIF(Sheet1!$D$4, Sheet2!$C$2:$C$21)+COUNTIF(Sheet1!$E$4, Sheet2!$D$2:$D$21)), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), ""), ROW(A1))

becomes

SMALL({"";"";3;""; "";"";"";"";""; "";"";"";"";"";""; "";"";"";"";""}, ROW(A1))

and returns 3.

Step 4 - Return corresponding value from data table on sheet 2

The INDEX function returns a value based on a cell reference and column/row numbers.

INDEX(Sheet2!$A$2:$D$21, SMALL(IF(COUNTA($B$4:$E$4)=(COUNTIF($B$4, Sheet2!$A$2:$A$21)+COUNTIF(Sheet1!$C$4, Sheet2!$B$2:$B$21)+COUNTIF(Sheet1!$D$4, Sheet2!$C$2:$C$21)+COUNTIF(Sheet1!$E$4, Sheet2!$D$2:$D$21)), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), ""), ROW(A1)), COLUMN(A1))

becomes

INDEX(Sheet2!$A$2:$D$21, 3, COLUMN(A1))

becomes

INDEX(Sheet2!$A$2:$D$21, 3, 1)

and returns Keisha.

Step 5 - Check if there are more than 0 (zero) citeria

The COUNTA function counts the non-empty or blank cells in a cell range.

COUNTA($B$4:$E$4)<>0

becomes

2<>0

and returns TRUE

Step 6 - Return value if there are more than zero criteria

IF(COUNTA($B$4:$E$4)<>0, INDEX(Sheet2!$A$2:$D$21, SMALL(IF(COUNTA($B$4:$E$4)=(COUNTIF($B$4, Sheet2!$A$2:$A$21)+COUNTIF(Sheet1!$C$4, Sheet2!$B$2:$B$21)+COUNTIF(Sheet1!$D$4, Sheet2!$C$2:$C$21)+COUNTIF(Sheet1!$E$4, Sheet2!$D$2:$D$21)), MATCH(ROW(Sheet2!$D$2:$D$21), ROW(Sheet2!$D$2:$D$21)), ""), ROW(A1)), COLUMN(A1)), "")

becomes

IF(TRUE, "Keisha", "")

and returns "Keisha" in cell B8.

Back to top

Lookup with an unknown number of criteria [Advanced Filter]

Lookup with an unknown number of criteria new advanced filter1

The image above shows the filtered data in-place using a condition on row 3. It uses Excel's built-in feature named Advanced Filter.

There are no formulas in this example. I made room for conditions above the data list. I copied the header names to a row above the data.

Note, the conditions may disappear after the filter is applied if you place them next to the list to the right or left.

Here is how to set it up.

  1. Go to tab "Data" on the ribbon.
  2. Click the "Advanced" button. A dialog box appears.
    Lookup with an unknown number of criteria new advanced filter dialog box
  3. You can filter the list in-place or copy to another location. I chose to filter in-place.
  4. The list range lets you select the data you want to filter.
    Note, select the column header names as well.
  5. Select your criteria in "The Criteria range:". Don't forget to include your column header names.
  6. Click OK button.

Lookup with an unknown number of criteria new advanced filter

The image above shows the filtered list, the colored row numbers indicate that the list is filtered. Click the "Clear" button on the ribbon to delete the filter, all data will be visible again.

I recommend you check out the Advanced Filter category if this feature is interesting for you.

Back to top

Excel Table and slicers

Lookup with an unknown number of criteria new slicers

The image above shows the data set converted to an Excel Table and four slicers above the Excel Table. Slicers were introduced in Excel version 2010.

They allow you to quickly filter data by clicking on values in the slicer. You can only use slicers with Excel Tables and Pivot Tables, however, both Excel Tables and Slicers are really easy to create. Let us begin creating an Excel Table.

How to convert a data set to an Excel Table

Lookup with an unknown number of criteria new create Excel Table

  1. Select any cell in the data set.
  2. Press CTRL + T to open the "Create Table" dialog box. See image above.
  3. Enable checkbox "My Table has headers" if true.
  4. Click OK button.

How to create Slicers

Lookup with an unknown number of criteria new insert slicer

  1. Select any cell in the Excel Table. A new tab named "Table Design" appears on the ribbon.
  2. Go to tab "Table Design" on the ribbon.
  3. Click "Insert Slicers" button. A dialog box appears, see image above. Click checkboxes to insert a slice for each, the names next to checkboxes are column header names.
  4. Click OK button.
  5. Excel inserts a slicer for each selected checkbox.
    Lookup with an unknown number of criteria new insert slicer1
  6. Move and resize each slicer.

How to select, move and resize slicers

Lookup with an unknown number of criteria new select slicer

Click with left mouse button on a slicer you want to select. A selected slicer shows sizing handles. They are white circles that appear on each corner and side.

Lookup with an unknown number of criteria new select multiple slicers

Press and hold CTRL key and then press with left mouse button on multiple slicers to select them. Selected slicers have sizing handles visible.

To move a slicing handle click and hold on a selected slicer and then drag with mouse to a new location. Release left mouse button to let go.

To resize a slicers click and hold on any sizing handle, then drag with mouse to resize.

Back to top

Back to top

Recommended articles