0

I have one sheet (e.g. Sheet 1) where there are two columns:

  • Enterprise Number
  • Enterprise Name

and in the second sheet (e.g. Sheet 2), I have only Enterprise Name column. The problem is that the Enterprise Name in the Sheet 2 is not exactly just the name of the enterprise, but is often chopped by commas, since most cells contain both the short and the longer name of the enterprise.

So what I believe would be a good solution is to, firstly, check whether the enterprise name from Sheet 1 is contained in the column in Sheet 2, and if so - assign that enterprise number from Sheet 1 to the more general enterprise name in Sheet 2.

For example:

SHEET 1

  • Enterprise Number
  • EE_123123
  • EE_321321

  • Enterprise Name

  • Jacob's Ent.
  • Mark's Ent.

SHEET 2

  • Enterprise Name
  • Jacob's Ent., Jacob's Enterprise
  • Mark's Ent., Mark's Enterprise

  • Enterprise Number

  • [empty]

So, as you can see, the name from Sheet 1 can be contained in the name in Sheet 2.

How would you achieve this?

Thanks for help!

3
  • New members commonly mistake this for a service site where we will do the work. It is a Q&A community where specific questions are asked after you have attempted something and get stuck. Please add details of what you have tried so far, including scripts, code or formulas, and we will try to help. If you need more info about asking questions, check out How to Ask in the help center.
    – CharlieRB
    Mar 16, 2016 at 12:50
  • I can't even start! What I would do is probably use the MATCH function, or a function that has something to see whether a string contains a string, but that's it! Mar 16, 2016 at 13:02
  • For your two examples, you could use text to columns to split on the comma and then use INDEX/MATCH
    – Kyle
    Mar 16, 2016 at 14:16

1 Answer 1

1

Disclaimer: Without knowing the layout of your data, this is a general answer derived from the example data you have given.

You can use INDEX/MATCH combination. Also, to clean the text up on Sheet 2 for an exact match, you can use the LEFT and FIND functions. Here is what it looks like.

=INDEX(Sheet1!A:A, MATCH(LEFT(A2, FIND(",", A2)-1),Sheet1!B:B,0))

enter image description here

This looks at the values in column A of Sheet 1. The MATCH looks for the matching text, but first the LEFT and FIND functions clean up the text so it is only looking for the portion left of the comma. Then it looks in column B of sheet 1 for a match. If a match is found, it returns the value of column A.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .