3

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

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.

5
  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.