Formula Error: JOIN, SUBSTITUTE, FIND, VLOOKUP

Vivien Chong

Earlier someone in this community helped me with the above formula. However, I am unable to find the question trait for further reference.

JOIN column: Reference from Sheet A to join 5 columns of data

CONTACT column: Using Vlookup to reference the contact list in Sheet A

=VLOOKUP([Employee ID]@row, {Employee Listing Range}, 3, false)

However, there's error in the rows highlighted pink box. Vlookup is picking on wrong information and the following columns are all in error.

I also finds it difficult to understand the formulas in Branch, Company and Department.

Branch: =LEFT(SUBSTITUTE($JOIN@row, JOIN($Name@row:Name@row, "/") + "/", ""), FIND("/", SUBSTITUTE($JOIN@row, JOIN($Name@row:Name@row, "/") + "/", "")) - 1)

Company: =LEFT(SUBSTITUTE($JOIN@row, JOIN($Name@row:Branch@row, "/") + "/", ""), FIND("/", SUBSTITUTE($JOIN@row, JOIN($Name@row:Branch@row, "/") + "/", "")) - 1)

Department: =LEFT(SUBSTITUTE($JOIN@row, JOIN($Name@row:Company@row, "/") + "/", ""), FIND("/", SUBSTITUTE($JOIN@row, JOIN($Name@row:Company@row, "/") + "/", "")) - 1)

1) I would like to know what could have gone wrong with the Vlookup formula, why isn't it picking the correct information.

2) I would like to understand how the formula in Branch, Company and Department works.

Thank you.

Paul Newcome

Ugh. And looking at it, that makes sense why the Benjamin Lye row isn't working properly. In our Substitute function, we are telling it to remove ALL instances of the text string. Well since "Rick Benjamin Lye" shows up twice, it is being removed twice. Let's try tweaking that SUBSTITUTE.

=LEFT(SUBSTITUTE($JOIN@row, JOIN($Name@row:Name@row, "/") + "/", "", 1), FIND("/", SUBSTITUTE($JOIN@row, JOIN($Name@row:Name@row, "/") + "/", "", 1)) - 1)

Ok. So now that we have that portion working at least... Lets try to tackle this Contact column issue. What if we insert a contact type column either to the left or right of the parsing solution and put your VLOOKUP in there, then just hide the column in the parsing solution where the contact portion is being pulled.

Or you could remove the contact portion from the string in the JOIN column pull entirely and use the VLOOKUP.

Paul Newcome

For the VLOOKUP... Is it pulling the wrong name based on the correct Employee ID, or is the Employee ID wrong which in turn gives the VLOOKUP the wrong information to match to?

.

Now for the longer answer to #2...

The formulas in the Branch, Company, and Department columns are parsing out the data from the JOIN column. They all work based on the same concept and were designed to be dragfilled across multiple columns. The catch here though... Those formulas are designed so that each piece of data is put into the next column. It is not designed to have a column in the middle (in this case your Contact Column) that is "skipped".

I'll break down the formula in the Department column as that will be easier to explain, but they all three work the same way.

First lets take a look at this portion:

JOIN($Name@row:Company@row, "/") + "/"

This works exactly as it reads. You are joining the data in every cell beginning in the Name column and ending with the Company column and using the forward slash as the delimiter (same delimiter used in the JOIN column).

In the first row for Vivien Chong, that outputs

"Vivien Chong/Vivien Chong/BSR/57N/"

Next we wrap that output in a SUBSTITUTE function and basically say to remove that string from the JOIN column. ONce we remove that from the string in the JOIN column, we are left with "SS/", so we use a LEFT function to pull the leftmost character from that string all the way up to the character just before the first "/".

That gives us an output of "SS" which is the next "portion" of the string in the JOIN column.

If you move to the Company column, you will see that the range in the JOIN function is pulling in the columns from the left stating with Name and ending with the column just before it (Branch). Using that same row, the JOIN gives us

""Vivien Chong/Vivien Chong/BSR/"

We remove that form the string in the JOIN column and are left with

"57N/SS/"

Use the LEFT/FIND to pull the leftmost characters up to just before the first "/", and our output is

"57N".

Is all of this making sense as to how these formulas work to parse out data from a delimited string?

Now lets take a look at the differences between the Vivien Chong row and the ABD RAHMAN BIN MOHAMED row.

Vivien Chong/Vivien Chong

vs

ABD RAHMAN BIN MOHAMED/111@gmail.com

See how Vivien Chong is the first two data points whereas in the other row it goes straight from name to email instead of repeating the name? Lets take a look at how this impacts the parsing solution based on the above.

Text String:

ABD RAHMAN BIN MOHAMED/111@gmail.com/PK/APMSS

Once we get to the Company column, the JOIN function is producing a string of

ABD RAHMAN BIN MOHAMED/ABD RAHMAN BIN MOHAMED/111@gmail.com

See the problem? We aren't removing anything from the original text string in the JOIN column now because the text string generated by the JOIN function in the Company column doesn't exist in the original string. Make sense? That's why beginning in the Company column and moving further to the right with this series of formulas is just going to keep repeating the name. We aren't removing anything from the original string, so the LEFT/FIND that pulls up to the first "/" is going to keep pulling that first data point (the name).

To correct that particular issue, you are going to want to make sure that the formula in your JOIN column is outputting that name twice. To do that you are going to want to start with the source data.

Vivien Chong

@Paul Newcome thanks for your elaborated explanation to help me understand how the formula works.

On the vlookup formula for CONTACT column, it is pulling information from the source sheet.

The JOIN columns shows the information in the source sheet: Vivien Chong/Vivien Chong/BSR/57N/SS

First column is name, second column is email. In my source sheet, Vivien Chong is in the contact list, so it appears as name instead of email.

For this last row: ABD RAHMAN BIN MOHAMED/111@gmail.com/PK/APMSS

It's name followed by email, as the email is not in the contact list, so it is showing the actual email instead. But the VLOOKUP is returning the name which is rather funny. I have changed this CONTACT column to CONTACT LIST or TEXT/NUMBER and it is still the same.

When I change the contact in the source sheet to "testing testing" for the first row:

Vivien Chong/testing testing/BSR/57N/SS

The same error happens:

I am still trying to figure out what could have gone wrong here.

Paul Newcome

The reason you are now having an issue with the Vivian Chong row is the same reason as above.

The SUBSTITUTE/JOIN in the Branch column is trying to remove the string

"Vivien Chong/testing testing"

But the string in the JOIN column doesn't contain that. Since it isn't removing anything form the original string in the JOIN column, it is going to pull the first data point "Vivien Chong".

The data in the column labeled "Contact" must be an exact match to the second portion of the string in the JOIN column.

So it looks like the issue is with the VLOOKUP. AS you said... It is kind of odd that it is an email in the source sheet but pulls across as a name. Let's try this...

Forget about the VLOOKUP. Move the formula from the Branch column into the Contact column. Then dragfill it to the right to get it to update the other columns. This should pull the second data point from the string into the Contact column. This way we know it matches what is being pulled from the source sheet instead of trying to break up the formula pattern with a VLOOKUP right in the middle of the parsing solution.

Vivien Chong

@Paul Newcome I have tried what you suggested.

This time, row one with Vivien Chong/testing testing is correct.

But row two with Rick Benjamin Lye/Rick Benjamin Lye is incorrect.

It seems that with the VLOOKUP formula it works when the JOIN content is: Vivien Chong/Vivien Chong

But there will be error for Vivien Chong/testing testing

The SUBSTITUTE formula works when the JOIN content is Vivien Chong/testing testing

but there will be error for Vivien Chong/Vivien Chong

Anyhow, to get to the root of the issue, we actually want to pull the contact list from source sheet to this destination sheet, however we find that whether VLOOKUP or JOIN/SUBSTITUTE formula, it is just pulling the text and not the contact list.

What I can now think of is to remove the contact from the JOIN list, and so the Name, Branch, Company, Department should be in order.

But how can I retrieve the contact list from the source sheet with reference to the Staff ID@row?

As the first VLOOKUP formula (=VLOOKUP([Employee ID]@row, {Employee Listing Range}, 3, false)) shows that it is pulling the wrong information and it is not a contact list, just a text.

Any advice or assistance would be helpful. Thanks Paul in advance.

Paul Newcome

Ugh. And looking at it, that makes sense why the Benjamin Lye row isn't working properly. In our Substitute function, we are telling it to remove ALL instances of the text string. Well since "Rick Benjamin Lye" shows up twice, it is being removed twice. Let's try tweaking that SUBSTITUTE.

=LEFT(SUBSTITUTE($JOIN@row, JOIN($Name@row:Name@row, "/") + "/", "", 1), FIND("/", SUBSTITUTE($JOIN@row, JOIN($Name@row:Name@row, "/") + "/", "", 1)) - 1)

Ok. So now that we have that portion working at least... Lets try to tackle this Contact column issue. What if we insert a contact type column either to the left or right of the parsing solution and put your VLOOKUP in there, then just hide the column in the parsing solution where the contact portion is being pulled.

Or you could remove the contact portion from the string in the JOIN column pull entirely and use the VLOOKUP.

Vivien Chong

Thanks @Paul Newcome . I have remove the Contact List column to the side and using Vlookup to pull the contact list. And I realised I still need to put a Contact column there so that the entire formula across other columns can work well. Thanks for the HIDE THE COLUMN tips. :D

Paul Newcome

Happy to help. 👍️

So it sounds like you are up and running with no issues now?