robmprager
New Member
- Joined
- May 31, 2017
- Messages
- 3
Hi all
The below formula found on Search for a text string and return multiple adjacent values | Get Digital Help - Microsoft Excel resource returns values in adjacent cells based on one criteria and the contents of one column. So for example, if you are searching for the word pen in one column, the formula returns the contents of an adjacent cell.
=INDEX($B$1:$B$5, SMALL(IF(ISNUMBER(SEARCH($E$1, $A$1:$A$5)), MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5))), ROW(A1)))
I would like to amend this formula so it enables me to have two criteria, (say a search for the word pen in one column and a search for the word blue, which is located in F1 in another column, say column C) and then return the adjacent cell.
The below formula found on Search for a text string and return multiple adjacent values | Get Digital Help - Microsoft Excel resource returns values in adjacent cells based on one criteria and the contents of one column. So for example, if you are searching for the word pen in one column, the formula returns the contents of an adjacent cell.
=INDEX($B$1:$B$5, SMALL(IF(ISNUMBER(SEARCH($E$1, $A$1:$A$5)), MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5))), ROW(A1)))
I would like to amend this formula so it enables me to have two criteria, (say a search for the word pen in one column and a search for the word blue, which is located in F1 in another column, say column C) and then return the adjacent cell.