Filter a long list by a shorter list of values, keeping repeated values

I am trying to use the filters and vlookup, but I haven't gotten this to work in my excel 07.

I have 2 sheets. Sheet 2 has a list of numbers that I want to check for in sheet 1, and then pull any matches including duplicates, to sheet 2. For example,

Sheet 1
1
2
3
3
4
5
6


Sheet 2
2
3
5

I want to use the numbers in sheet 2 as the criteria for pulling from sheet 1 and create a new column with those values. So my result for the above would be

Sheet 2
2
3
3
5

I've been working with advanced filters, and trying to set the criteria through there. I thought that simply having the criteria in separate rows would use the OR function, but the list I get doesn't work. Am I doing something wrong, or is there a better way to do this? I've tried with my numbers stored as both text and numbers.

Edit: as a bonus, I will then need to pull information from the cells adjacent to each value from sheet one. Using the same example as above,

Sheet 2
2|A
3|A
3|B
5|D

Where the A, A, B, and D are taken from cells adjacent to the respective values in sheet 1.

In Sheet1, in a new column, say column C, start at C2 and enter formula:

=IF(ISNUMBER(MATCH(A2,Sheet2!$A$2:$A$4,0)),COUNT(C$1:C1)+1,"")

copied down.. where Sheet2, A2:A4 hold the check values.

Then in Sheet2, enter formula somewhere away from A2:A4:

=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($A$5:$A5),Sheet1!$C:$C,0)),"")

copied down as far as you need and across to get more row info.

Great, thanks! You've already saved me hours of tedious work. I have found out how to manipulate the first formula, and it numbers all the matches in the order they are encountered in the main list.

=IFERROR(INDEX(Sheet1!A:A,MATCH(ROWS($A$5:$A5),Sheet1!$C:$C,0)),"")

What is the MATCH(ROWS($A$5:$A5),Sheet1!$C:$C,0) doing? I know the $ essentially "locks" the reference, but why $A$5:$A5, for example? What values should I replace A5:A5 with for my data?

In case specifics help, my reference list is in "Sheet1" and extends from A2:A369. The complete list is on "Sheet" and extends from A2 into the thousands (say 1670). Here is what I tried to do:

I put the following equation starting in E2. =IFERROR(INDEX(Sheet!A:A,MATCH(ROWS($A$5:$A5),Sheet1!$C:$C,0)),"") gives me a number, but where did this number come from? It wasn't one on my list of values, but I assume that is because ROWS should reference something other than A5:A5. Could you give me some (very) basic info that would help me manipulate this formula?

Ed: I was able to just sort by the arrangement given by the first equation and get the data in the adjacent cells by simple copy/paste. I am still curious as to how the second equation works and what it does, but you've allowed me to move forward as much as I need to. Thanks again.

I assumed the data in your "Sheet1" to match to the values in Sheet2 are in column A.. .if not you need to adjust the INDEX(Sheet1!A:A part to suit the column the data is to be extracted from.

Did the first formula:


=IF(ISNUMBER(MATCH(A2,Sheet2!$A$2:$A$4,0)),COUNT(C$1:C1)+1,"") give results? This formula just consecutively numbers the items in columm A that match the numbers in Sheet2, A2:A4.

Make sure your sheetnames correspond to your actual sheetnames too... mine were defaults.

The ROWS($A$5:$A5) serve as a counter.. notice the second $A5 doesn't have a $ before the 5... so when you copy formula down, it turns to $A6, $A7 and so on.. ROWS() counts number of rows between to cells.. so A5:A5 is 1, A5:A6 is 2, A5:A7 is 3, etc.. these number results are matched to the "key" column we created in the first sheet, so that the corresponding values from column A are retrieved...

Copying formula across changes the A:A to B:B so it gets the next's column's values...

See the attached

Yes you have it all right.

The A5 is arbitrary. I usually pick the cell reference that I am putting the formulas in.. so that it sticks the area.. in case you make changes elsewhere it wouldn't skew the results here...