Match and extract data query

Copper Contributor

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:

 

MS Forum Question 23-9-19.jpg

4 Replies

@TurboTim 

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

@TurboTim 

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 )

), "" )

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.

 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.