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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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
411
Replies
5
Views
68
Replies
5
Views
160
Replies
5
Views
505
Replies
6
Views
53

1,130,362
Messages
5,641,697
Members
417,229
Latest member
BODYCOTE

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.

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