Return Multiple Vertical values into same colum from another sheet

[tazmtiger]

Hi Everyone

I am having a difficult time trying to figure out how to accomplish the following task.

I have a parts inventory, or parts storage workbook that tracks where parts are kept, or where they are located at. It utilizes part Numbers and a BiN# locations to Identify where the parts are kept or located at. (But in some cases), we store the same Part number in different BIN#s. In other words: (Multiple BIN#'s) For same part number.
All of this parts are tracked and managed through various excel sheets within the same workbook. And recently, the BOSS came with a requirement, to make the Part Number location accessible from all worksheets though the use of the INDEX function.
I am using this formula code -----> =IF(AC7="","",IF(ISNA(VLOOKUP($AC$4,'Parts Room'!$C$13:$G$2691,4,FALSE)),"",VLOOKUP($AC$4,'Parts Room'!$C$6:$G$2691,5,FALSE))) in my attempt to make that requirement possible, but this code does not seem to work right. "No matter what I do", it only gives me the first Bin# location and it does not display the rest bin# locations for the same part. Is there something I am missing? All the "Part numbers" are in the "C Column" and the Bin# locations are in the "G Column" in a sheet called Parts Room. I need all the Bin Locations available for the part of choice "Placed in one column" in any of the other sheets as desired.

Can any one here help? I would really appreciate anyone's help on this subject.

Thank you very much in advance for any assistance you can prove to resolve this issue.

Thank you!

 

[Coding4Fun]

You are running into the limitations of VLOOKUP being that it will only return the first value that it finds. So you either need to combine multiple fields to create a unique lookup value maybe create a new field by combining the Bin and the Part number and then use the new value for the VLOOKUP.

You might find use with this site, I had a similar issue and this helped me to find a solution but is a bit complex. This is not using VLOOKUP.

http://www.globaliconnect.com/excel...lues-for-one-lookup-value&catid=77&Itemid=473

 

[tazmtiger]

You are running into the limitations of VLOOKUP being that it will only return the first value that it finds. So you either need to combine multiple fields to create a unique lookup value maybe create a new field by combining the Bin and the Part number and then use the new value for the VLOOKUP.

You might find use with this site, I had a similar issue and this helped me to find a solution but is a bit complex. This is not using VLOOKUP.

http://www.globaliconnect.com/excel...lues-for-one-lookup-value&catid=77&Itemid=473

Thank you for your assistance;

I figured it out.

I used the following Formula string method, and work beautifully.

D1=IF(ISERROR(INDEX(Sheet2!$A$1:$B$200,SMALL(IF(Sheet2!$A$1:$A$200=Sheet1!$E$1,ROW(Sheet2!$A$1:$A$200)),ROW(1:1)),2)),"",INDEX(Sheet2!$A$1:$B$200,SMALL(IF(Sheet2!$A$1:$A$200=Sheet1!$E$1,ROW(Sheet2!$A$1:$A$200)),ROW(1:1)),2))
-----------------------------------------------------------------------------------------------------------------------------------------
D2=IF(ISERROR(INDEX(Sheet2!$A$1:$B$200,SMALL(IF(Sheet2!$A$1:$A$200=Sheet1!$E$1,ROW(Sheet2!$A$1:$A$200)),ROW(2:2)),2)),"",INDEX(Sheet2!$A$1:$B$200,SMALL(IF(Sheet2!$A$1:$A$200=Sheet1!$E$1,ROW(Sheet2!$A$1:$A$200)),ROW(2:2)),2))

Etc, Etc, … And So forth.

Thank you for your time.

 

[Coding4Fun]

Glad you were able to make sense of it and it worked for you