2

I am trying to get this sort of result, where I check on sheet1 if the first three numbers on column A are found anywhere in the sheet2's column A. If the numbers are found then on column B excel prints Yes and if it is not found excel prints No:

Sheet 1

    A         B  
    =12300    Yes
    =56700    Yes
    =89200    No

Sheet 2

    A
    =12345
    =56789
    =44498

The formula that I have at the moment on sheet1 col B is 

=IF(ISNUMBER(MATCH(A1;Sheet2!A:A));"Yes";"No")

How to add the possibility to check that it takes into consideration only the 3 first characters in both sheets?

waspe
  • 23
  • 4

3 Answers3

2

You can initially try:

=IF(ISNUMBER(MATCH(LEFT(A1,3),LEFT(Sheet2!A1:A3,3),0)),"YES","NO")

And make sure to enter as array through CtrlShiftEnter

Notice the reference of the used range, with array formulas you would want to try and avoid whole column references because of performance issues. In this specific case we could make it dynamic through:

=IF(ISNUMBER(MATCH(LEFT(A1,3),LEFT(Sheet2!A1:INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A)),3),0)),"YES","NO")

Now we got a dynamic formula, we might as well try to avoid the use of having to enter it as an array formula. We could implement an INDEX to take care of that issue:

=IF(ISNUMBER(MATCH(LEFT(A1,3),INDEX(LEFT(Sheet2!A1:INDEX(Sheet2!A:A,COUNTA(Sheet2!A:A)),3),),0)),"YES","NO")

So there we have it; a dynamic array formula that avoids having to enter as such =)

If your values actually start with a = then you should reference the first 4 characters of the value with LEFT. And to also take into consideration your parameters are seperated through semi-colon, the final formula would look like:

=IF(ISNUMBER(MATCH(LEFT(A1;4);INDEX(LEFT(Sheet2!A1:INDEX(Sheet2!A:A;COUNTA(Sheet2!A:A));4););0));"YES";"NO")
JvdV
  • 70,606
  • 8
  • 39
  • 70
1

Alternatively, you can also use a SUMPRODUCT:

=IF(SUMPRODUCT(--(LEFT(A1,3)=LEFT(Sheet2!$A$1:$A$3,3))),"Yes","No")

enter image description here

Justyna MK
  • 3,523
  • 3
  • 11
  • 25
  • For some reason this is not working for me. If I have =12477 in sheet2 and =12500 on sheet 1 I still get Yes. I have to use ";" instead of ",". I don´t know if it makes any difference in this case. I am not very familiar with excel. – waspe Oct 08 '19 at 09:45
  • 1
    It makes a huge difference @waspe :). Exchange the comma's with a semi-colon. – JvdV Oct 08 '19 at 09:46
  • Agreed with @JvdV, depending on your local settings you might need to replace all `,` with `;`. If it still does not work please paste a screenshot and we'll try to help. – Justyna MK Oct 08 '19 at 09:51
  • Yes I switched all your commas to semi-colons in my formula. But it still does not work in my case. – waspe Oct 08 '19 at 09:52
  • Can you paste a screenshot please? As you can see it works on my side -> [link](https://i.imgur.com/kzOMILw.png) – Justyna MK Oct 08 '19 at 09:59
  • 1
    Your values actually have a `=` in front, so change the 3 in the `LEFT` function to a 4 if you want to include the equal operator @waspe – JvdV Oct 08 '19 at 10:11
  • Well now I just feel kind of stupid :D . Thanks! One more thing: There´s quite a lot of data and I should check the whole col in sheet 2. Is this the right way: `=IF(SUMPRODUCT(--(LEFT(A1;4)=LEFT(Sheet2!A:A;4)));"Yes";"No")` – waspe Oct 08 '19 at 10:18
  • 1
    @waspe, try and avoid whole column references. Whether you have a lot of data or not, it's probably not over a million rows long. In other words, referencing a whole column using array formulas will slow down your workbook significantly. – JvdV Oct 08 '19 at 10:36
  • 1
    That is correct, however, as @JvdV already indicated in his post, we strongly recommend limiting the number of rows where the search is performed. The last row in Excel is `1048576` so, unless you have that much data, restrict your range like `LEFT(Sheet2!$A$1:$A$1000;4)` – Justyna MK Oct 08 '19 at 10:37
1

There is many perfect answers to this question, but it can also be done by adding a wildcard to the end of your string instead of the actual value:

=IF(ISNUMBER(MATCH(LEFT(A1,3)&"*",Sheet2!A:A,0)),"Yes","No")

As @JvdV pointed out, this doesn't work properly for numeric values, only for text. As per his suggestion:

=IF(ISNUMBER(MATCH(LEFT(A1,3)&"*",INDEX(TEXT(Sheet1!A:A,"0"),),0)),"Yes","No")

Would do to omit this issue. Also please read the comments for his suggestion not to use a whole column as reference.

Plutian
  • 2,276
  • 3
  • 14
  • 23
  • I'm afraid this won't work as intended Plutian. Have you tried it and got the results you wanted? – JvdV Oct 08 '19 at 09:45
  • I have, several times. It takes the first three characters from the cell and either does or does not find a match in the range. If this won't work, can you elaborate? I'd love to learn what I've done wrong. – Plutian Oct 08 '19 at 09:48
  • 2
    To me, the following changes would make it work: `=IF(ISNUMBER(MATCH(LEFT(A1,3)&"*",TEXT(Sheet1!A:A,"0"),0)),"Yes","No")` entered as array formula. You'll need to compare text values against a text value, otherwise there would never be a matched value. Interesting approach though with the wildcard. Note that whole column references will slow this thing down significantly =) – JvdV Oct 08 '19 at 09:54
  • 1
    @JvdV I see, I've tried to play about with mine as well, and came to the same conclusion, that it doesn't work properly for numeric values. Thank you for pointing it out. I'll use this knowledge for later, and for now I'll update my answer with your suggestion. – Plutian Oct 08 '19 at 09:58
  • Just one more thing, as per my last comment; just check the difference in calculation time using a whole column reference and just the three cells. That would tell you that whole column references on array formulas are not good for performance. Either way, in your current solution: `=IF(ISNUMBER(MATCH(LEFT(A1,3)&"*",INDEX(TEXT(Sheet1!A:A,"0"),),0)),"Yes","No")` would prevent having to enter the formula through ctrl+shift+enter – JvdV Oct 08 '19 at 10:05
  • If my initial answer had worked, referring to a whole column wouldn't be much of an issue. However to account for text and numeric values like you did it definitely will. Thank you for suggesting these edits, since I'm here to learn as much as anyone. You've definitely given me some insight and things to play about with. – Plutian Oct 08 '19 at 10:17
  • No worries Plutian. I've noticed you pick up on things quickly. And the wildcard suggestion is a valid one, just a bit difficult on numeric values, but if there really are equal signs in front of OP's data, it might become a very neat approach (since his data should be text). Therefor +. OT: I specifically liked the trick with `INDEX` to avoid the use of ctrl+shift+enter. You'll notice it can come in handy more often =) – JvdV Oct 08 '19 at 10:19
  • Yes I picked up on that one, very neat trick when array formulas are almost unavoidable. Definitely will play about with that and add it to my concept files. +1 for you as well for teaching me something new. – Plutian Oct 08 '19 at 10:25
  • BTW, if OP's values have an equal sign (as per the other answer's comments), then your initial answer is very usefull > `=IF(ISNUMBER(MATCH(LEFT(A1;4)&"*";Sheet2!A:A;0));"YES";"NO")` – JvdV Oct 08 '19 at 10:27
  • @JvdV True, but I find it is always best to account for all situations like you did, to avoid a long debugging process and many head scratches or possible downvotes in the case of non compliant data. – Plutian Oct 08 '19 at 10:30