Question about if this excel/google sheet function is possible

Hi,

So I'm trying to create a function for work so I can't give exact details (which makes it hard) but essentially I'm trying to create a long URL.

On my google sheet, I have two sheets, Sheet A & Sheet B.

On Sheet A there are 7 columns.

• 1st column can be left empty depending on if I need something from Sheet B which I will get into later.
• 2nd -6th column I enter different things and are required. It concatenates everything key terms and whatever is added into those cells. These columns cannot be changed as they are the minimum requirement for this excel sheet to work.
• 7th column spits out the URL I need. I have a IF function to say if 1st column is empty, then concatenate key terms and values in 2nd to 6th column. If 1st column has a value, I have a v lookup to pull an exact match from Sheet B, then concatenate key terms and values in 2nd to 6th column.

On Sheet B I have parts of a URL that are needed if there is a value in the 1st column in Sheet A.

• 1st column contains the value entered into the 1st column on Sheet A.
• 2nd column shows the part of the URL that the 7th column from Sheet A will spit out if 1st column from Sheet B is entered.

The part of the URL shown in the 2nd column of Sheet B is comprised of two things. The first part of it is what category the value in 1st column is (product, gender, age, language, etc), and the second part is the actual value. I will be calling this (A) and (B) respectively. These are separated by an & sign.

My goal is to be able to combine multiple Sheet B 1st column values together, and connect them to the concatenated URL in Sheet A.

The 3 problems I have that need to be fixed are:

• When multiple Sheet B 1st column values are combined together, the URL is broken into category then actual value. For example if I want green & plants, the URL is category (in this case, color and product) then green&plants. So the resulting URL is something like color1&product2&1=green&2=plants . It also adds a number after the category as shown in the URL on the left.
• When I have multiple values that are of the same category, it is separated by a %7C. For example if I want green & blue, the URL is color&green%7Cblue .
• The other issue is, on Sheet A 1st column, I want to be able to add multiple values and pull multiple values from Sheet B with the above two problems solved, in 1 cell. So two or more values in a cell.

I can also add a sample spreadsheet that does not have all the URL's I need for work but some other random words. This is a very confusing question and I would like to get this solved if possible!