Vlookup Multiple Values ...Index? Please Help :)

[keith81591]

Hello,Excel Friends!

I have a large file and I am trying to determine all possible locations (Column G) for any given SKU (Column A).

Please see the attached sample file.

In some cases, I have SKUs that appear in column A multiple times, that have different location values (Column G).
I am hopeful that I can return all possible location values into column K (I have provided an example of that I would like this to look like in cell K11 & K12.) In most cases, I have no more than three locations per SKU.

I am hopeful to not only have a formula for this situation but to better understand how it works in the future. I am not very advanced when it comes to excel. Vlookup and SumIF is about as far as I have gone!

Any assistance you can provide on this would be greatly appreciated!

- Keith, R

[Glenn Kennedy]

If you want them all in one cell you will need one of the following:

1. VBA ( a UDF).. saved as .xlsm

2. Helper column.

3. Upgrade to O365.

If you're Ok with them in separate columns:

=IFERROR(INDEX($I:$I,AGGREGATE(15,6,ROW($A$2:$A$40)/($A$2:$A$40=$A2),COLUMNS($K$2:K2)))&"","")

copied across and down. Adjust the range (the bits in red) as needed. Don't go mad and use whole column references there, unless you do have 1,000,000 rows.

[keith81591]

Glen, I really appreciate your help! I was able to use the formula you provided to pull the data into separate columns. I can run a =CONCAT if I choose to pull them together.
Our Warehouse guys are doing inventory checks on select items and I wanted to pull all possible locations for each SKU.

Thanks again!! :D

Keith R.

[keith81591]

If you want them all in one cell you will need one of the following:

1. VBA ( a UDF).. saved as .xlsm

2. Helper column.

3. Upgrade to O365.

If you're Ok with them in separate columns:

=IFERROR(INDEX($I:$I,AGGREGATE(15,6,ROW($A$2:$A$40)/($A$2:$A$40=$A2),COLUMNS($K$2:K2)))&"","")

copied across and down. Adjust the range (the bits in red) as needed. Don't go mad and use whole column references there, unless you do have 1,000,000 rows.

Glen, I really appreciate your help! I was able to use the formula you provided to pull the data into separate columns. I can run a =CONCAT if I choose to pull them together.
Our Warehouse guys are doing inventory checks on select items and I wanted to pull all possible locations for each SKU.

Thanks again!! :D

Keith R.

[Glenn Kennedy]

woo Hooo! You're welcome and thnaks for the rep.