VLookup multiple data in one cell outcome desired

Multiple Vlookup in Google Sheets

I have a Vlookup working for a column with the thing I'm looking for, then two columns in a different sheet with sheetname! where it looks for the thing I'm looking for and gives me back the ID #. So like...
=VLOOKUP(B2,sheetname!A$2:B$19121,2,FALSE)
for
33270 VNY business name 31199
So B2 is what I'm looking for in the sheetname sheet in two columns where one column is the post ID and one column has what's in B2.

For a single value it works great.

But now I have the need to find multiple ids for multiple business codes.

So let's say I have 30 location codes which match up to 1 business ID.
So I have a column for a listing with 30 location codes and on the same row the business ID

This business ID has 30 locations. I want the ID's of all of the locations to show up in one column.The locations are 3 letter codes which are all in one cell.

So the row/columns look like:
This is what I have to work with:

Business Post ID | Business Location 3 letter code
12345 | ABC, XYZ, RFT, PTR, BBC, QRZ

In the second sheet is the list of the Business Location 3 letter codes and their Post IDs

Business Location | Business Location POST ID
ABC | 56789
XYZ | 37560
RFT | 25491
PTR | 89976
BBC | 44538
QRZ | 99852

The goals is to have a new column which gives me just one cell which tells me all of the business location post IDs for that business Post ID.

So what I want is something that looks like:

Business Post ID |. Business Location Post IDs
12345. | 56789,37560,99852

Right now for the single version which works great I am using
=VLOOKUP(B2,sheetname!A$2:B$19121,2,FALSE)
in which the sheet is the list of businesses with their post ids and B2 is the column with the business 3 letter code. The second sheet (sheetname) has a column of all 3 letter codes with a second column with that 3 letter code's post id.

Does that make sense? I want to take that theory but have it look up all of the post ids for all of the 3 letter codes in the cell.

The data is being given to me where it's the business post id and a cell with say 70 3 letter code location, each of which have their own Post id in the other sheet/column.


If anyone can help I'd sure appreciate it!

Also I tried this from that link above with
I tried this =TEXTJOIN(", ",TRUE,IF(B2=sheetname!$A$2:$A$1920,$B$2:$B$1920,"")) where sheetname A is the business 3 letter code and column B is 3 letter code post ID But it didn't work.

Thanks so much in advance for any help!

Thank you!
I moved things around a little trying to make it more simple. I'm playing with your formula but can't quite get it to work (because I changed it in the meantime of course).

A. B. C. D.
TR_ID TR_location_id. AP_location_id AP_ID
33270 VNY,ABC,DEF,XYZ TN41 13596

So the 4 columns are now all on one sheet, A,B,C,D.
B: I want to know the id from column D of if what is in B is found in C

Desired outcome: A new column with all of the ID's from D if B's are found in C.

Would you mind tweaking the formula? I keep playing with it and failing.

Thank you so very much!

Well it found results in about 1/3 of them which isn't quite it but close because that is exactly opposite of what we are looking for.
So the B column will 100% of the time be found in the C column. The C column is the master list of all 3 letter code locations.
Column A is a subset of those. We want the ids of the locations in C which are held in D.

I think we are close we just need to switch around some column IDs?

Oh wow...get this. I'm sure this will make sense to you and I'd love to understand why this worked.
I had your formula in column G.
I pasted it again in Column H so I could play with the column letters without breaking what you had and guess what...it gave me the exact right results. Why would putting the same formula in a column one column over make it work?

However, if I add a second location id to B, so ABC, DEF rather than just ABC (and both are definitely contained in column C) it only gives me the id from D in the results column for the first one, ABC...not idabc,iddef.

Attachment 782559
Maybe easier if you can see it.
Column C has the master list. Column D is the Post ID of each item in the master list in C.
Column A is the Post id of the location ids in Column B.
I want column E or whatever column to show me:
Look up whats in column B in Column C, and tell me the AP_ID/Column D in the new column of that look up.
So the first row of the results column should say 13596,13597

Sure I have one ready. How should I do it? Publish with a link?

It won't let me post the url as I'm new to the forum, it says I have to post a few times. Is there another way to get it to you?

I sent it to you in a private message.

This turned out the be a solution which works great if anyone else needs it:

=ARRAYFORMULA(Join(",",VLOOKUP(SPLIT(B2,",",TRUE,TRUE),C:D,2,False)))

OK sorry. Thank you for your time, it's much appreciated.

Thanks, Jan!