Home

Match and extract data query

%3CLINGO-SUB%20id%3D%22lingo-sub-869729%22%20slang%3D%22en-US%22%3EMatch%20and%20extract%20data%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869729%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20per%20the%20image%2C%20I%20want%20a%20sheet%20to%20search%20another%20sheet%20and%20return%20only%20cells%20containing%20a%20%22D%22.%3C%2FP%3E%3CP%3EThese%20results%20are%20to%20populate%20the%20second%20sheet%20in%20consecutive%20columns.%20I%20got%20given%20an%20array%20answer%20which%20works%20on%20a%20different%20platform%2C%20but%20not%20in%20excel.%20Any%20help%20very%20much%20appreciated%2C%20thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20reference%2C%20here%20is%20what%20I%20was%20given%3A%3C%2FP%3E%3CP%3E%7B%3DIFERROR(INDEX('Sheet1'!%24B2%3A%24AF2%2CSMALL(IF(ISNUMBER(FIND(%22D%22%2C'Sheet1'!%24B2%3A%24L2))%2CCOLUMN('Sheet1'!%24B%242%3A%24AF%242)-COLUMN(%24B%242)%2B1)%2CCOLUMN()-1))%2C%22%22)%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20saw%20it%20today%20in%20the%20online%20browser%20the%20same%20formula%20actually%20appears%20as%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%3D%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EARRAY_CONSTRAIN%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EARRAYFORMULA%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIFERROR%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EINDEX%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3E'Sheet1'!%24B2%3A%24AF2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ESMALL%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EIF%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EISNUMBER%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EFIND%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22D%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%3E'Sheet1'!%24B2%3A%24AF2%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ECOLUMN%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3E'Sheet1'!%24B%242%3A%24AF%242%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E-%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ECOLUMN%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%3E%24B%242%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E%2B%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3ECOLUMN%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E(%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20%20default-formula-text-color%22%3E-%3C%2FSPAN%3E%3CSPAN%20class%3D%22number%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20string%20%22%3E%22%22%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22number%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E%2C%3C%2FSPAN%3E%20%3CSPAN%20class%3D%22number%22%3E1%3C%2FSPAN%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3E)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%20default-formula-text-color%22%3EI%20am%20struggling%20to%20understand%20how%20the%20array%20search%20works%2C%20I%20know%20what%20it%20means%2C%20but%20am%20now%20confused%20with%20how%20excel%20would%20do%20this%20as%20pictured%20below%3A%3C%2FSPAN%3E%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20857px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F133103i80031982D5B0ABEB%2Fimage-dimensions%2F857x426%3Fv%3D1.0%22%20width%3D%22857%22%20height%3D%22426%22%20alt%3D%22MS%20Forum%20Question%2023-9-19.jpg%22%20title%3D%22MS%20Forum%20Question%2023-9-19.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-869729%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869773%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20and%20extract%20data%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869773%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F413408%22%20target%3D%22_blank%22%3E%40TurboTim%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20would%20be%20easier%20for%20you%20to%20obtain%20answers%20if%20you%20attach%20your%20sample%20Excel%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-869950%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20and%20extract%20data%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-869950%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F413408%22%20target%3D%22_blank%22%3E%40TurboTim%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20me%20the%20starting%20point%20is%20to%20apply%20a%20name%20'DataRow'%20to%20a%20row%20of%20table%20as%20a%20relative%20reference%20that%20refers%20to%3C%2FP%3E%3CP%3E%3D%26nbsp%3B%3CSPAN%3E%24B2%3A%24AF2%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3E(assuming%20the%20active%20cell%20to%20be%20in%20row%202)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EIn%20my%20version%20of%20Excel%2C%20I%20have%20the%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%20function%20so%20the%20solution%20is%20given%20by%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20FILTER(DataRow%2C%20LEFT(DataRow%2C1)%3D%22D%22%2C%22%22)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3E%3CFONT%3EWithout%20%3CSTRONG%3EFILTER%3C%2FSTRONG%3E%2C%20things%20get%20kind%20of%20tedious.%26nbsp%3B%20One%20way%20is%20to%20return%20a%20column%20number%20'k'%20and%20then%20use%20%3CSTRONG%3ESMALL%3C%2FSTRONG%3E%20to%20pack%20the%20list%20down%3C%2FFONT%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20SMALL(%20IF(%20LEFT(DataRow%2C1)%3D%22D%22%2C%20k%20)%2C%20k%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3EFrom%20there%20it%20is%20just%20a%20matter%20of%20returning%20the%20matched%20codes%20by%20index%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20IFERROR(%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3CSTRONG%3E%3CFONT%3EINDEX(%20DataRow%2C%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3ESMALL(%20IF(%20LEFT(DataRow%2C1)%3D%22D%22%2C%20k%20)%2C%20k%20)%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E)%2C%20%3C%2FFONT%3E%3C%2FSTRONG%3E%3CSTRONG%3E%3CFONT%3E%22%22%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871288%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20and%20extract%20data%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871288%22%20slang%3D%22en-US%22%3EPoint%20taken%2C%20thanks%20Twifoo.%20I%20thought%20enough%20info%20was%20there%20in%20the%20picture%20but%20I%20appreciate%20what%20you%20are%20saying%20to%20make%20it%20easier%20for%20someone%20to%20work%20on.%3CBR%20%2F%3EI%20figured%20out%20something%20that%20worked%20btw%2C%20posting%20below.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-871291%22%20slang%3D%22en-US%22%3ERe%3A%20Match%20and%20extract%20data%20query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-871291%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3BAt%20work%20now%2C%20will%20have%20a%20play%20with%20this%20later%20to%20see%20if%20it%20does%20the%20same%20thing%20-%20Thank%20you%20for%20your%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20figured%20this%20out%20last%20minute%20yesterday%20and%20it%20seemed%20to%20work%20for%20me%2C%20entering%20into%20sheet%202%20at%20cell%20B2%20where%20sheet%201%20is%20named%20'Entire%20Sheet'%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3DIFERROR(INDEX('Entire%20Sheet'!%24B2%3A%24AF2%2CSMALL(IF(ISNUMBER(FIND(%22D%22%2C'Entire%20Sheet'!%24B2%3A%24AF2))%2CCOLUMN('Entire%20Sheet'!%24B%242%3A%24AF%242)-COLUMN(%24B%242)%2B1)%2CCOLUMN()-1))%2C%22%22)%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20saw%20a%20note%20about%20a%20September%20update%20where%20you%20don't%20need%20CTRL%2BSHIFT%2BENTER%20to%20give%20the%20curly%20array%20brackets%2C%20I%20still%20had%20to%20use%20that%20to%20get%20the%20curly%20brackets%20though.%3C%2FP%3E%3C%2FLINGO-BODY%3E
TurboTim
New 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.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
description for autoplay blocking in settings page
HotCakeX in Discussions on
8 Replies