extract specific data from a list

[pacificstorm82]

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.

[#2]

To enable formula testing, please attach your sample file.

[#3]

@Twifoo 

Find file attached. TY

[#4]

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

[#5]

@Twifoo 

[#6 · Accepted solution]

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.

[#7]

@Twifoo 

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

[#8]

@Twifoo 

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.

[#9]

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

[#10]

@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?

[#11]

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

[#12]

@Twifoo 

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.