• 547K Members
• 5,229 Online
• 654K Conversations
SOLVED

## extract specific data from a list

Highlighted
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.

12 Replies

Highlighted

# Re: extract specific data from a list

Find file attached. TY

Highlighted

# 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.
Highlighted

Highlighted
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.
Highlighted

# Re: extract specific data from a list

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

Highlighted

# 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.

Highlighted

# 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.
Highlighted

# 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.
Highlighted

# 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.

Highlighted

# Re: extract specific data from a list

Related Conversations