# Array formula to return adjacent cell based on multiple criteria

#### robmprager

##### New Member
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.

### Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

#### BarryL

##### Well-known Member
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

##### Well-known Member
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

##### New Member
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

##### New Member

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

##### Well-known Member
=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

##### New Member
=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?

Replies
1
Views
317
Replies
5
Views
437
Replies
5
Views
143
Replies
0
Views
66
Replies
3
Views
198

1,127,711
Messages
5,626,422
Members
416,183
Latest member
IanA

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back