Array formula to return adjacent cell based on multiple criteria

[robmprager]

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.

 

[BarryL]

try

=INDEX($B$1:$B$5, SMALL(IF(AND(ISNUMBER(SEARCH($E$1, $A$1:$A$5)),ISNUMBER(SEARCH($F$1, $C$1:$C$5))), MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5))), ROW(A1)))

 

[XOR LX]

try

=INDEX($B$1:$B$5, SMALL(IF(AND(ISNUMBER(SEARCH($E$1, $A$1:$A$5)),ISNUMBER(SEARCH($F$1, $C$1:$C$5))), MATCH(ROW($A$1:$A$5), ROW($A$1:$A$5))), ROW(A1)))

AND does not function like that within array formulas; it only ever returns a single value.

Regards

 

[robmprager]

AND does not function like that within array formulas; it only ever returns a single value.

Regards

Thanks - XOR LX. Is there a workaround you would recommend?

 

[lander2]

Thanks - XOR LX. Is there a workaround you would recommend?

Try and adapt this - looks up using 2 criteria -


Note this is an array formula so use CTRL SHIFT ENTER

=INDEX(FormData,MATCH(1,(RefDate=$D$3)*(Number=$H$3),0),3)

FormData is a named range for the data I want the formula to look up - you could write it $A$1:$Z$30 if you wanted,

RefDate is again a named range for the specific column within FormData that I want to look at to find a match to the first criteria

$D$3 is the cell reference for the first criteria I want to match

Number is another named range - this time for the 2nd column withing FormData that I want to look at to find a match to the 2nd criteria

$H$3 is the cell reference for the 2nd criteria I want to match

the 3 is the column reference within FormData from which I want to get the result.

 

[XOR LX]

=INDEX(B:B,SMALL(IF(ISNUMBER(SEARCH(E$1,A$1:A$5)),IF(ISNUMBER(SEARCH(F$1,C$1:C$5)),ROW(C$1:C$5))),ROWS($1:1)))

and copied down.

Regards

 

[robmprager]

=INDEX(B:B,SMALL(IF(ISNUMBER(SEARCH(E$1,A$1:A$5)),IF(ISNUMBER(SEARCH(F$1,C$1:C$5)),ROW(C$1:C$5))),ROWS($1:1)))

and copied down.

Regards

Hi there - I've tried this formula in both array (Ctrl-Shift-Enter) and non array form and it does not return the correct result.

I've found a formula which does return the correct results this website Lookup values in a range using two or more criteria and return multiple matches in excel, part 2 | Get Digital Help - Microsoft Excel resource

But, it seems to be causing the spreadsheet to hang when updating other tabs and seems to be quite resource intensive?

Is there a possible alternative?