Array formula to return adjacent cell based on multiple criteria

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.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

BarryL

Well-known Member
Joined
Jan 20, 2014
Messages
1,436
Office Version
  1. 2019
Platform
  1. Windows
  2. MacOS
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
Joined
Jul 2, 2012
Messages
4,517
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
 

lander2

New Member
Joined
May 4, 2017
Messages
24
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 2, 2012
Messages
4,517
=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
Joined
May 31, 2017
Messages
3
=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?
 

Watch MrExcel Video

Forum statistics

Threads
1,129,331
Messages
5,635,662
Members
416,871
Latest member
jbcpub

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top