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",""),""))
Improve this question
2
  • And the question is…?
    – Alex
    Commented Mar 6, 2016 at 6:28
  • Hi Bimantree, why don't you prepare an example sheet for us to work with?
    – Jacob Jan
    Commented Mar 6, 2016 at 7:17

1 Answer 1

Reset to default
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.

Improve this answer
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
    Commented Mar 6, 2016 at 6:57
  • So, you should make sure there is nothing in the cells below the one with the formula.
    – user79865
    Commented Mar 6, 2016 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
    Commented Mar 6, 2016 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
    Commented Mar 6, 2016 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
    Commented Mar 6, 2016 at 17:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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