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...
- 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.
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.
=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.
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.
- You’re very much welcome. To access help on the function, select the cell, press F2, and click the function name.
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?
Much appreciated attempt but the list in F are all the same serial numbers, not individual.