This blog article is one out of five articles on the same subject.

Read more related articles in the archive.

Question: I want to search two columns with two search strings? The strings can be anywhere in these two columns but they both have to be somewhere on the same row to match. The search is not case sensitive.

Answer:

multiple-criteria-lookup-part2

Formula in D6:D17:

=INDEX(B2:B16, SMALL(IF(ISNUMBER(SEARCH($E$2, A2:A16)*SEARCH($E$3, A2:A16))+ISNUMBER(SEARCH($E$2, B2:B16)*SEARCH($E$3, B2:B16))+ISNUMBER(SEARCH($E$2, A2:A16)*SEARCH($E$3, B2:B16))+ISNUMBER(SEARCH($E$3, A2:A16)*SEARCH($E$2, B2:B16))>0, ROW(A2:A16)-1, ""), ROW()-5)) + CTRL + SHIFT + ENTER

Formula in E6:E17:

=INDEX(A2:A16, SMALL(IF(ISNUMBER(SEARCH($E$2, A2:A16)*SEARCH($E$3, A2:A16))+ISNUMBER(SEARCH($E$2, B2:B16)*SEARCH($E$3, B2:B16))+ISNUMBER(SEARCH($E$2, A2:A16)*SEARCH($E$3, B2:B16))+ISNUMBER(SEARCH($E$3, A2:A16)*SEARCH($E$2, B2:B16))>0, ROW(A2:A16)-1, ""), ROW()-5)) + CTRL + SHIFT + ENTER

Download excel sample file for this tutorial.
multiple-criteria-lookup-with-multiple-results-2
(Excel 97-2003 Workbook *.xls)

Functions used in this article

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

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

ROW(reference) returns the rownumber of a reference

COUNT(value1;[value2])
Counts the number of cells in a range that contain numbers

SMALL(array,k) returns the k-th smallest row number in this data set.

ISNUMBER(value)
Checks whether a value is a number and returns TRUE or FALSE

SUM(number1,[number2],)
Adds all the numbers in a range of cells

SEARCH() Returns the number of the character at which a specific character or text string is first found, reading left to right (not case sensitive)

This blog article is one out of five articles on the same subject.

Read more related articles in the archive.

Share and Enjoy:
  • Share/Bookmark

Related posts:

  1. Lookup with multiple criteria and display multiple search results using excel formula
  2. Lookup with multiple criteria and display multiple search results using excel formula, part 3
  3. Lookup with multiple criteria and display multiple search results using excel formula, part 4
  4. Search for a cell value in an excel table
  5. Search for a text string in an excel table
  6. Vlookup with 2 or more lookup criteria and return multiple matches in excel
  7. Search and display all cells that contain all search strings in excel
  8. Search and display a range of values in excel
  9. Lookup values in a range using two or more criteria and return multiple matches in excel