# Partial match for strings that are truncated beyond 30 characters

I have two sheets. Sheet one has a list of events in column N: "IEM Katowice", and others. However, our system imports event names that are longer than 30 letters (including spaces) with a `..` at the end (e.g. `CEVO Gfinity Professional Seas..`).

Sheet two contains two columns, A which has the full name for all Online events and B which has the full name for all LAN events. For example, "IEM Katowice" would be in column B, while "CEVO Gfinity Professional Season 9" would be in column A.

I am trying to make column U of sheet one find the partial match for column E (the event) to any event in column A and B. Based on which column it finds the partial match in it would then say `LAN` or `Online` in the cell in column U.

I have been playing with MATCH formulas and others such as:

``````=ARRAYFORMULA(IFERROR(IF(VLOOKUP(N:N,Format!B:B,{1}*SIGN(ROW(N:N)),0)=N:N,"LAN",""),""))
``````
• And the question is…? – Alex Mar 6 '16 at 6:28
• Hi Bimantree, why don't you prepare an example sheet for us to work with? – Jacob Jan Tuinstra Mar 6 '16 at 7:17

## 1 Answer

You want to test the equality of the first 30 characters of each string. The command `left` can be used for this:

``````=arrayformula(if(iserror(vlookup(left(N:N, 30), left(Format!B:B,30), 1, False)), "that", "this"))
``````

The formula returns "this" if there is a match in column B, and "that" otherwise.

• Thanks for the help. This looks like exactly the formula I want. I am torubleshooting it now, as it returns: ERROR: Array result was not expanded because it would overwrite data in "next row down" – Bigmantree Mar 6 '16 at 6:57
• So, you should make sure there is nothing in the cells below the one with the formula. – user79865 Mar 6 '16 at 7:22
• Yup, the entire column is blank. I only changed where it is pulling from (moved things on the second sheet to column D. I'm currently having no luck figuring out the issue. Formula I am using is =arrayformula(if(iserror(vlookup(left(N:N, 30), left(Format!D:D,30), 1, False)), "that", "this")) I will change that and this to its respective LAN or Online once I know the formula is working. Will keep plugging at it, thanks for all thew help Sally :) – Bigmantree Mar 6 '16 at 10:40
• Are you entering the formula in the 1st row of the sheet? (If not, it won't have enough room to expand). What exactly does the error message say? – user79865 Mar 6 '16 at 16:22
• Hey Sally. Thanks a ton for the help. I was indeed not entering the formula in the first row but attempting to drag the formula through all rows, quite silly of me. It is working perfectly now. Thanks a ton for all the help, it is really appreciated! – Bigmantree Mar 6 '16 at 17:11