Forum Discussion
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
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 )
), "" )
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.
- 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.
