0

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 goal 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.

I see this https://www.extendoffice.com/documents/excel/2706-excel-vlookup-return-multiple-values-in-one-cell.html But I'm not sure that it's the right track or quite how to implement.

If anyone can help I'd sure appreciate it!

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.

3
  • 1
    Welcome to Web Applications. What web app are you using?
    – Rubén
    Jun 1 at 17:14
  • Sorry...Google Sheets, should have said that. I did get a solution from a Reddit poster... =ARRAYFORMULA(Join(",",VLOOKUP(SPLIT(B2,",",TRUE,TRUE),C:D,2,False))) if that helps anyone else.
    – sheetfun11
    Jun 2 at 14:48
  • Thanks for you reply. Please post the solution as an answer as comments have less visibility and they are intended for asking/providing some clarification to help making the post more clear / helpful.
    – Rubén
    Jun 2 at 16:24

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.