Index lookup + multiple entrys + wildcard

Hi everybody

if i have a tabel with

a : b
Apple : 1
Apple pie : 2
Tomato : 4

I want to make a table which returns me 1 & 2 below in seperate cells

to do that i have the following
=INDEX($C$2:$C$6, SMALL(IF(COUNTIF($B$8,"*"&$B$2:$B$6&"*"), ROW($B$2:$B$6)-MIN(ROW($B$2:$B$6))+1, ""), ROW(A1)))

Where B8 is the refence cell Apple

i wanted to include a wild card ( *Apple)
However that did not work

would anybody have an ide how to solve this ?

I want to pull the formular until no values containg apple are left. '

also wehen i try your formular it returns that there is an error and excel selcts B3

did the formualr work with you ?

Originally Posted by Fotis1991

With youe dat in range A1:B3, use this ARRAY formula

=INDEX(B1:B3;SMALL(IF(A1:A3="*Apple*";ROW(A1:A3));ROW(A1)))

I want to pull the formular until no values containg apple are left. '

also wehen i try your formular it returns that there is an error and excel selcts B3

did the formualr work with you ?

Originally Posted by abousetta

Why don't you use vlookup instead? Have a look at the attachment and a thread I have devoted to using advanced vlookup.

Hope this helps.

abousetta

hm that would work but the actual list has 2000 entrys and i want to retrive the all data for a specific firms (apple) in a seperate sheet


ps. how do i deleat a post ( posted the same thing twice)

Originally Posted by Fotis1991

Do you really use Excel 2003? If yes change my IFERROR formula with if(iserror...

Thanks that was a strocke of genius and worked perfectly