• 464K Members
• 7,755 Online
• 561K Conversations
SOLVED

## extract specific data from a list

Occasional Contributor

# extract specific data from a list

Hello,

=IFERROR(INDEX(Input!C:C,MATCH(0,IF(\$I\$2=Input!A:A,COUNTIF(\$I\$2:I3,Input!C:C),""),0)),"")

I am using this array formula to sort a list of many different serial numbers into a list containing one serial number of each. I would then also like to extract the serial numbers containing certain letters. I thought about using the following formula, =ISNUMBER(SEARCH(substring,text), but can't figure out where within the initial formula to place it.

Many thanks.

11 Replies

# Re: extract specific data from a list

To enable formula testing, please attach your sample file.

# Re: extract specific data from a list

Find file attached. TY

# Re: extract specific data from a list

Please manually enter your desired results in Column F. Then, attach your sample file again. I will work for the formula from there.

Solution

# Re: extract specific data from a list

I am replying via mobile phone so I can’t test this formula in D3, copied down rows:
=IFERROR(INDEX(B\$3:B\$100,
AGGREGATE(15,6,
1/((A\$3:A\$100=D\$2)*
(LEFT(B\$3:B\$100,2)=D\$1)*
(COUNTIF(D\$2:D2,B\$3:B\$100)=0))*
(ROW(A\$3:A\$100)-2),1)),””)
The foregoing formula assumes that the text “AB” is stored in D1 to avoid hard-coding thereof.

# Re: extract specific data from a list

Much appreciated attempt but the list in F are all the same serial numbers, not individual.

# Re: extract specific data from a list

Please ignore my previous post. Your formula works perfectly. I am not familiar with the use of aggregate but I will research it's functions to see how I can use them further.

Thank you for your responses.

# Re: extract specific data from a list

You’re very much welcome. To access help on the function, select the cell, press F2, and click the function name.

# Re: extract specific data from a list

@Twifoo

The formula works if I use data from the same worksheet but I tried to use the formula across different tabs and using columns/tables but it is not working. Would anything need to change for it to work?

Highlighted

# Re: extract specific data from a list

For the formula to work in other sheets, you must accordingly modify the references. Nonetheless, the logic shall remain the same.

# Re: extract specific data from a list

See attached revised file. Formula in column H on Sheet1 has been modified to include Sheet2 however the results are inconsistent, duplicated and, depending on which "Town" is entered into Sheet1 H2, may not even display an "AB" serial number. Many thanks for your assistance.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies