data fill sheet 2 from sheet 1 based on criteria

[FDDavis]

I am trying to Data fill sheet 2 based on criteria match from sheet 1.
I have no knowledge of VBA and/or macros and very limited knowledg
working with arrays.
see sample data below

sheet 1 note sheet 1 contains approx 2500 rows
column A column B
2295.60 sales.sam
17496 jobs.sue
14.45 trade.john
1775 trade.sam

Sheet 2 should look in column B for .sam or .john and when foun
return results to sheet 2 columns A and B
sheet 2 contain variable number of rows depending on occurrences o
sheet 1
Column A column B
2295.60 sales.sam
14.45 trade.john
1775 trade.sam

I have tried several options I think I need an array formula. I ca
find the data with the following array formula I have not worked on th
or statement yet to find sam or john I figured when I got it workin
for 1 then could modify to add the or for the other
In sheet 2 column A entered with cntrl shift enter
{=INDEX('Sheet 1'!$A$1:$A$3000,MATCH(TRUE,ISNUMBER(FIND("sam",'Shee
1'!$B$1:$B$3000)),0))}
in sheet 2 column B entered with cntrl shift enter
{=INDEX('Sheet 1'!$B$1:$B$3000,MATCH(TRUE,ISNUMBER(FIND("sam",'Shee
1'!$B$1:$B$3000)),0))}
but when I drag it down it keeps finding the same entry so I have to d
something different. Maybe I am approaching the solution wrong
I apologize for the lengthy question
Thanks in Advance for any help F

 

[Frank Kabel]

Hi
the problem is, MATCH always returns the first match (and not the
following)

 

[FDDavis]

Frank first THANK you for your response but I am a real basic user o
excel and don't understand what I should use in place of MATCH

 

[FDDavis]

I am at a loss as what to do Frank if you see this could you explai
what you ment by the match statement please Thanks F

 

[Frank Kabel]

Hi
I would use the Advanced filter for this: 'Data - Filter - Advanced
filter' for this. Define your lookup criteria and put the result on a
separate sheet. As said: MATCH can only return the first value found in
your range but not multiple occurences

 

[FDDavis]

I have a couple of questions still I tried this and unless I am doing i
wrong i can only filter on 2 sets of criteria I need to filter on
different criteria. then next question, is this dynamic in that if th
data on sheet 1 changes will it automaticaly update sheet 2 or is thi
just a snapshot when I do the advanced filter function. It's What
would like is each time you open the workbook or make changes to dat
on sheet 1 that this be updated to match the filtered criteria on shee
2. Sorry for bewing so dense but I just don't understand Thanks for an
further hel

 

[Frank Kabel]

Hi
Advanced filters could use more than two criteria but this is not
dynamic

 

[FDDavis]

What approach would be best to acheive what I am trying to do. which i
make a sheet update dynamicaly from data posted in another sheet tha
matches a criteria?