Rashid asks:

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

Lookup using an unknown number of criteria

The data table is on sheet 2.

Array 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)), ""), "")

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

Step 1 - Count the number of cells in each table column that meet the criteria and then 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))

Step 2 - Count the number of criteria 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))

Step 3 - If TRUE return the row number

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)), "")

Step 4 - 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))

Step 5 - 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))

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(arrayk)
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(rangecriteria)
Counts the number of cells within a range that meet a single criterion that you specify.

MATCH(lookup_valuelookup_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.