VLOOKUP/MATCH With Multiple Matches

1
votes

I'm at a loss for how to do something which I imagine one of you gurus has done before and shouldn't be too challenging. I have two sheets in my workbook (Let's call them ITEMS and LOCS). Sheet ITEMS has a list of unique items in column A (roughly 10,000) and sheet LOCS has the items in column A and their locations in Column B. Now the issue arises since many items have multiple locations (up to five) and thus occupy multiple rows on the LOCS sheet. Is there a way to use some combination of VLOOKUP or MATCH in column B on my ITEMS sheet to get all the locations into one cell separated by a comma space. Obviously, if I put =VLOOKUP($A:$A, LOCS!$A:$B,2,0) into cell ITEMS!B1 it will only return the first location. Ideally, I would want it to display something like this: Shelf, Desk, Floor. Any ideas or pointers in the right direction would be greatly appreciated--thanks so much!

Sample Data (Columns A:B from sheet ITEMS and columns D:E are from sheet LOCS and I highlighted the desired outcome in col B in green)

2
excelexcel-formulavlookup
Post some sample data and desired result. – basic
I posted a little data for you all! Hope it helps make what I was asking more clear. I'm pretty new to VBA but am slowly getting the hang of it so if that's the best way to do it I would understand – bgensits
I am also sure that this should be possible through PowerQuery as another option besides formulas – JvdV

2 Answers

2
votes

If you have access to the new Dynamic Array Formulas you can use the following equation and drag down as needed:

Cell E2: =TEXTJOIN(", ",TRUE,FILTER(B2:B14,A2:A14=D2))

enter image description here

2
votes

If you are using Office 365 then you're in for a treat!

Here's my LOCS table -

enter image description here

And the array formula using TEXTJOIN (make sure to use Ctrl+Shift+Enter) -

=TEXTJOIN(", ",1,IF(LOCS!$A$2:$A$10=A2,LOCS!$B$2:$B$10,""))

The result -

enter image description here