Highlighted
New Contributor

# Match and extract data query

As per the image, I want a sheet to search another sheet and return only cells containing a "D".

These results are to populate the second sheet in consecutive columns. I got given an array answer which works on a different platform, but not in excel. Any help very much appreciated, thanks.

For reference, here is what I was given:

{=IFERROR(INDEX('Sheet1'!\$B2:\$AF2,SMALL(IF(ISNUMBER(FIND("D",'Sheet1'!\$B2:\$L2)),COLUMN('Sheet1'!\$B\$2:\$AF\$2)-COLUMN(\$B\$2)+1),COLUMN()-1)),"")}

When I saw it today in the online browser the same formula actually appears as:

=ARRAY_CONSTRAIN(ARRAYFORMULA(IFERROR(INDEX('Sheet1'!\$B2:\$AF2,SMALL(IF(ISNUMBER(FIND("D",'Sheet1'!\$B2:\$AF2)),COLUMN('Sheet1'!\$B\$2:\$AF\$2)-COLUMN(\$B\$2)+1),COLUMN()-1)),"")), 1, 1)

I am struggling to understand how the array search works, I know what it means, but am now confused with how excel would do this as pictured below:

4 Replies
Highlighted

# Re: Match and extract data query

It would be easier for you to obtain answers if you attach your sample Excel file.

Highlighted

# Re: Match and extract data query

For me the starting point is to apply a name 'DataRow' to a row of table as a relative reference that refers to

\$B2:\$AF2

(assuming the active cell to be in row 2)

In my version of Excel, I have the FILTER function so the solution is given by

= FILTER(DataRow, LEFT(DataRow,1)="D","")

Without FILTER, things get kind of tedious.  One way is to return a column number 'k' and then use SMALL to pack the list down

= SMALL( IF( LEFT(DataRow,1)="D", k ), k )

From there it is just a matter of returning the matched codes by index

= IFERROR( INDEX( DataRow,

SMALL( IF( LEFT(DataRow,1)="D", k ), k )

), "" )

Highlighted

# Re: Match and extract data query

Point taken, thanks Twifoo. I thought enough info was there in the picture but I appreciate what you are saying to make it easier for someone to work on.
I figured out something that worked btw, posting below.
Highlighted

# Re: Match and extract data query

At work now, will have a play with this later to see if it does the same thing - Thank you for your answer.

I figured this out last minute yesterday and it seemed to work for me, entering into sheet 2 at cell B2 where sheet 1 is named 'Entire Sheet'

{=IFERROR(INDEX('Entire Sheet'!\$B2:\$AF2,SMALL(IF(ISNUMBER(FIND("D",'Entire Sheet'!\$B2:\$AF2)),COLUMN('Entire Sheet'!\$B\$2:\$AF\$2)-COLUMN(\$B\$2)+1),COLUMN()-1)),"")}

I saw a note about a September update where you don't need CTRL+SHIFT+ENTER to give the curly array brackets, I still had to use that to get the curly brackets though.