All cells containing the same word in a list

[JasperH90]

Hi,

I'm trying to imitate a kind of filter through means of a formula. I have a list with products and their respective quantities, but now I want to filter all articles with a similar description, f.e. cereals. But some of these cells are abbreviated, so the cell could also contain "cer."
Now I want to type "cer" in cel A1 in an empty worksheet. And get a list with articles containing this description in a list below, with their quantities.

A B C
1 Artikelnummer Omschrijving Intek#
2 5779 AARN.ABDIJHAM GER.1/2 1002 24
3 129561 ASPERGE-HAMBROODJE 4ST 3
4 908012 AV GEH.CORD. BLEU 2X110G15090 1
5 908038 AV GEH.CORDON BLEU 3X110G15090 1

So this above would be on sheet 2, and in sheet one I want to type in Cord. and retrieve values B4-C5. I have been looking and trying all day, but I just can manage to get it working.

Would be great if someone has a solution! Thanks

 

[doublej_jj]

Sheet 1 Cell A1 has your lookup value CORD

Sheet 2 (Your data sheet):

Insert a column before you description: So I have:
Row 1 Headers for
Column A is # (Your Sequential Numbers)
Column B is Key (newly inserted blank column)
Column C is Description
Column D is Quantity
So data really starts in row 2

B2 =IF(ISERROR(FIND(Sheet1!$A$1,C2,1)),0,1)
B3 =IF(ISERROR(FIND(Sheet1!$A$1,C3,1)),B2,B2+1)
Copy B3 down to all of your data

What we just did was look for your Lookup value, CORD in your example, and each time it is found, increment our key by 1.

Back on Sheet 1

A1 = CORD
B1 = Description
C1 = Quantity

A2 through A21 = 1 thru 20 (or however much you need)

B2=IF(ISERROR(VLOOKUP($A2,Sheet2!$B$2:$D$6,2,0)),"",VLOOKUP($A2,Sheet2!$B$2:$D$6,2,0))

C2 =IF(ISERROR(VLOOKUP($A2,Sheet2!$B$2:$D$6,3,0)),"",VLOOKUP($A2,Sheet2!$B$2:$D$6,3,0))

This does a look up on the number and when it finds the First one, it returns the description / quantity.

There may be a more elegant solution, but this does work.

Jeff

 

[JasperH90]

Great! Thanks a lot! I'm going to try it now

 

[JasperH90]

Just filled it in, and it works great! I changed the Key column (B) to column A, so I could hide it, and did the same with column A on sheet 1.