Google Sheets – Partial Match for Strings Truncated Beyond 30 Characters

google sheetsvlookup

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",""),""))

Best 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.

Related Topic