Forum Discussion
Solved
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.
- 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.
12 Replies
- To enable formula testing, please attach your sample file.
- Hi, I was wondering if you'd help me with this issue?
https://techcommunity.microsoft.com/t5/excel/need-help-with-creating-a-formula/m-p/1142068#M50808 Find file attached. TY
- Please manually enter your desired results in Column F. Then, attach your sample file again. I will work for the formula from there.
