Vlookup to transfer multiple duplicates to a seperate sheet

[astraybullet]

I am making a spreadsheet for a physical inventory. The information in sheet 1 if the material that I am tracking. The "F" column is the location in the warehouse each item is in (B1, B2, B3 etc are the row names in the warehouse). We use a printed sheet that we hang on each row that tells you every item in that row. The spreadsheet in sheet 2 is the physical inventory tag that needs the data transfered to the proper fields. In sheet 2, I have five fields that need filled in based on data in sheet 1. I need help because of all the duplicate "Warehouse Row" names that are listed in sheet 1. Sheet 3 is an example of what the end result needs to look like.



1.) On Sheet 2 Have A9 look at the value in C1
2.) Take that value and find the first value on sheet 1 and return the information that is in the A column of the same row into A9 on sheet 2.
3.) Then on Sheet 2 have A10 look at the value in C1
4.) Take that value and find the second value on sheet 1 and return information that is in the A column of the same row into A10 on sheet 2.

OR

1.) On sheet 2 have A9:A35 take the value in C1
2.) Find all the values of "Sheet 2 - C1" in the F column of sheet 1 and return all values from the A column for each matching item into Sheet 2 - A9:A35

I'm familiar w/Vlookup, but I haven't used much of any "IS" or "Index" functions. I'm also thinking maybe an Array formula?? I'm also not too familiar with array formulas.

The reason I'm not using a basic "Input" function is that this inventory is constantly being edited and the amount of material in each row varies widely.

Here is my example: vlookup tape tags.xlsx

[Teethless mama]

End Result sheet, A9:

=IFERROR(INDEX('Sheet 1'!A:A,SMALL(IF('Sheet 1'!$F$3:$F$17=$C$1,ROW('Sheet 1'!$F$3:$F$17)),ROWS($A$9:$A9))),"")

Array Formula: Press Ctrl+Shift+Enter, not just Enter
copy down and across

[dredwolf]

Try this Array Formula (Cntrl+Shift+Enter to enter,not just Enter) in A9 of sheet2:

Please Login or Register  to view this content.

Drag it down and to the right, it should return all the information for you

Hope this helps

Edit
Aah, Mama beat me to it ...lol

[astraybullet]

Wow! That works so well! I know a lot more about Index, Row, and Excel logic thanks to that one! Thank you both!

[dredwolf]

You are welcome !