Extracting values containing criteria from 1 column

0
votes

I have a column

Row   |Header  
------+------  
  001 | ABC  
  002 | AC  
  003 | AD  
  004 | BCA  
  005 | DBC

The way my sheet works is user types in criteria *BC* into one cell.

My VLOOKUP references said cell (with value *BC* in it).

Expected Result:

ABC
BCA
DBC

Actual Result

I understand that VLOOKUP for criteria 'BC' will only return 'ABC'.

I need to absolute reference the cell which the user types in the criteria, but I wish to return a list of values.

How would I go about approaching this? Array formula doesn't work correctly AFAIK.

1
excel

1 Answers

1
votes

If on has access to the dynamic array formulas(Currently only available to Office 365 Insiders):

=FILTER(B2:B6,ISNUMBER(SEARCH(D1,B2:B6)))

Put that in the first cell and the rest will fill automatically.

enter image description here

If not then INDEX/AGGREGATE should do it:

=IFERROR(INDEX(B:B,AGGREGATE(15,7,ROW($B$2:$B$6)/(ISNUMBER(SEARCH($D$1,B$2:$B$6))),ROW(1:1))),"")

Put that in the first cell and copy down till one gets blanks.

enter image description here

one LARGE Note: The use of SEARCH makes the use of * in the lookup not needed as it will search the string regardless.