- 513K Members
- 2,552 Online
- 611K Conversations

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

- Home
- :
- Excel
- :
- General Discussion
- :
- Match and extract data query

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-22-2019 11:19 PM

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:

Labels:

4 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-23-2019 12:26 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-23-2019 03:04 AM - edited 09-23-2019 03:08 AM

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 ) **

**), ****"" )**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-23-2019 06:22 PM

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.

I figured out something that worked btw, posting below.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-23-2019 06:29 PM

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

Sorting problem with imported data from query

DenisL
in
Excel
on
10-02-2019
227
Views

0 Likes

5 Replies

Excel web query DataFormat.Error "not a valid path"

Simon Pearce
in
Excel
on
05-01-2018
2,192
Views

0 Likes

7 Replies

Extract data from multiple sheet into one with conditions

TheCarb1
in
Excel
on
09-23-2019
176
Views

0 Likes

4 Replies

"Couldn't Update. You don't have permission to access the response data."

Stefon Simmons
in
Microsoft Forms
on
10-27-2016
1,912
Views

0 Likes

1 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © Microsoft