## Lookup with an unknown number of criteria

*Article last updated on January 26, 2018*

I used your array formula with great success to find the search results from multiple critera. 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 lets 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 before hand.

How would you go about solving this probem?

Your help is greatly appreciated, thanks so much!!

The data table is on sheet 2.

**Array formula in cell B8:**

**How to enter an array formula**

- Select cell B8
- Type the above array formula
- Press and hold CTRL + SHIFT
- Press Enter
- 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

- Copy cell B8
- Paste to C8:E8
- Copy cell range B8:E8
- Paste to cell range B9:E19

### Explaining the array formula in cell B8

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

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 there are two criteria matching on row 3 in the table

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

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

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

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

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.

### Download excel *.xlsx file

Lookup with an unknown number of predetermined criteria.xlsx

### Functions in this post:

**INDEX(**array,row_num,[column_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**SMALL(***array*, *k***)
**Returns the k-th smallest value in a data set

**IF(**logical_test, [value_if_true], [value_if_false]**)
**Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

**COUNTIF(***range*, *criteria***)**

Counts the number of cells within a range that meet a single criterion that you specify.

**MATCH(***lookup_value*, *lookup_array*, *[match_type]*)

Returns the relative position of an item in an array that matches a specified value in a specific order

**ROW(**reference**)**

Returns the row number of a reference.

Question: How do I create a chart that dynamically adds the values, as i type them on the worksheet? Answer: […]

Create dependent drop down lists containing unique distinct values

This article explains how to build dependent drop down lists. Here is a list of order numbers and products. We […]

Use dynamic ranges to automatically add new values

This post demonstrates how to automatically add new values to a drop down list and a chart. This tutorial contains […]

How to create a dynamic pivot table and refresh automatically

David Hager commented: Looks like an easy pivot table solution to me. Thanks for your comment! Now i know how to […]

Create a drop down list containing alphabetically sorted values

Overview This article describes how to create a drop down list populated with sorted values from A to Z. The […]

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves […]

Add or remove a value in a drop down list

Cell B3 contains a drop down list (Data Validation). The values in drop down list are from column H. I […]

The COUNTIF function is very capable of counting non-empty values. Column B above have a few blank cells, they are […]

How to use the COUNTA function

The COUNTA function counts the non-empty or blank cells in a cell reference. The picture above demonstrates the COUNTA function […]

### 4 Responses to “Lookup with an unknown number of criteria”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

Hi Oscar,

This is great and compliments your Multiple Criteria and Multiple Matches series of posts very well. However I have been trying to modify the array formulas in the 3rd and 4th posts of that series to cope with wildcard searches or part cell matches and thought this post may help but not so far unfortunately.

I have tried numerous approaches but can't seem to get a multiple criteria series of "searches" (or counts) to work with anything but exact data.

I'm trying to search for part of a serial number e.g.(A110 within a string A110E12694369020 as one of my search inputs. In this case it is always the first 4 digits but I'd like to be able to input just A1 or *A1* into a search cell and not rely on reviewing the first 4 characters as this is not always the result for these serial numbers.

I'm also looking for items with corresponding dates equal or before a certain due date search string - your 4th post to Lookup-with-multiple-criteria-and-display-multiple-search-results-using-excel-formula-part-4-john helped me resolve this criteria though.

Finally I'm trying another 'wildcard' search and to find FSS (or *FSS*) OR the exact inverse of this - so NOT containing FSS (or *FSS*) - and both of these search criteria are proving elusive to me.

All the data I have is in a large table but I'm not sure if I can alter it to be an Excel Table (which might help), as it is in a sheet that is deleted and replaced by an irregular macro from another workbook and this may corrupt a Table setup(?). It can not be a Pivot as the data is in a shared workbook and Pivot Tables do not update when in shared documents - hence the Array solution I am trying to develop. I have successfully named the columns of ranges I need though without loosing them on an update of data.

To summarise - I'd like to search by some form of wildcard; *app* to return a positive result for "apple" and to search for the opposite; have a criteria that would exclude "apple" using soemthing like *app*

Although I have around 80 columns I'm just looking to return the serial number in say Column A based on these multiple criteria; Col B <= a date, Col C does NOT contain a wildcard string *whatever*, Col D DOES contain a different wildcard string, Col E EXACTLY equals a third search string etc. I am happy to build up the criteria to more columns beyond this if I can resolve the NOT and the wildcard elements.

I'd really appreciate some further pointers, thanks Oscar!

Dear Oscar,

Ned Help!

How we can modify the above formula? as...

(the above example has 4 cols in Sheet2 and 4 criteria's to be searched in Sheet1 i.e. same No. of columns in both sheets and output is of same i.e. 4 cols)

What if we have 6 columns in data table in Sheet2 but same number of criteria i.e. 4 in sheet1 and we want to return the whole set of data from Sheet2 based on the criteria provided.

Thanks for your time and understanding.

Qadeer

Hello Mr Oscar,

I have the matter to create a megaformula to categorize my list. For short example:

A1: Cash in deposit (Branch A t/t)

A2: Borrowed from Corp. A

A3: Interest payment

A4: Int.panalty pmt

A5: Prin. Pmt

A6: Salary Pmt on April

A7: Sales abroad

A8: Branch C t/t

A9: Transferred from Company AA

A10: Mortgages to DD ltd

A11: Sal. Pmt on May

and at B1 cell, I create a formula as follows:

=IF(COUNT(SEARCH({"branch","corp.", "company"},A1))>0,"Precol.", IF(COUNT(SEARCH({"interest","int.", "prin."},A1))>0,"lo.",IF(COUNT(SEARCH("sales",A1))>0, "Sa.",IF(COUNT(SEARCH({"sal.", "Salary","wage","payroll"}, A1))>0,"Se.", "Others"))))

But, my formula is too long and too many parentheses.

I want to shorten this formula or replace by another. But how?

Could you please to solve my question?

Thank you very much.

Hung

Minh Hung

Thank you for your comment, I believe I have an answer for you:

https://www.get-digital-help.com/2017/07/07/nested-search/