INDEX MATCH with multiple criteria in string

Question

I want to do an INDEX MATCH function on google sheets. The MATCH function will have multiple criteria. My problem is the criteria is in a string, in a single cell.

How can I have the formula recognise each word in the string and run the INDEX MATCH function, returning all the results for which the MATCH of every word is true? Is there any way in which we can isolate each separate word in a string in a formula?

Here is an example spreadsheet.

In Sheet 1, there are a number of cities in column A. In column B, there are codes for each city.

In Sheet 2, I have a row for each of the codes, in column A. In column B, a list of the different cities in which the code exists. In column C, I would like to do the INDEX MATCH.

Basically, I would like to find out, for all codes in column A of sheet 2, all other codes which are in the same cities. If possible, I would like to order the codes from the one which has most cities in common with the reference code (in column A of Sheet 2).

Thank you

Answer 1

It is quite complicated, but working code below. It is done without sorting as it will add another layer of complexity. The problem lays in source data - they are not prepared for further operations - they should be changed to more "database like"

=IFERROR(
JOIN("; ",
  TRANSPOSE(
     UNIQUE(
        QUERY(
           TRANSPOSE(
              SPLIT(
                 JOIN("; ",
                      QUERY(Sheet1!A:B,"select B where A matches '"&JOIN("|",SPLIT(B2,", ",0,0))&"'",0)),"; ",0,1)),
         "select * where Col1<>'"&A2&"'",0)
        )
     )
   ),
"not found other codes")

I have build this soultion in your file

Is this is what you where looking for?