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