View original (French)

Excel VLOOKUP function that works except when it's too long!

Solved
Kahelm Posted messages 12 Registration date   Status Member Last intervention   -  
Kahelm Posted messages 12 Registration date   Status Member Last intervention   -
Hello,

Thank you for your insights, as I'm stuck on a function, here it is;

=IFERROR(VLOOKUP("&"*"&LEFT(C2,FIND("&" "&,C2)-1)&"*"&,D:D,1,0),VLOOKUP("&"*"&C2&"*"&,D:D,1,0))

This function allows you to search for the first word in cell C2 within column D, find the cell where the word is, and return the entire cell as a result.

The function WORKS, but only on cells with few characters.

For example, the word to search is Foot.

In D10, the word Foot is found, and the cell D10 is returned as a result.

However, when I add a lot of text in D10, the result remains #N/A, which is annoying.

Apparently, this part is causing the problem: FIND("&" "&,C2)-1

I’m stumped like a lizard in the sun.

Configuration: Windows 7 / Chrome 51.0.2704.84

6 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Indeed, after text, the search for text surrounded by asterisks no longer applies beyond 255 characters. This is not a formula issue, but likely a conceptual Excel problem in the implementation of the formula
    .
    We can partially work around this with an array formula such as, to take yours in English (tested in French with MID):

    =IFERROR(VLOOKUP("&"*"&"LEFT(C2,FIND(" ",C2)-1)&"*"&";MID(D:D,1,254),1,0),VLOOKUP("&"*"&C2&"*"&";MID(D:D,1,254),1,0))

    I say partially because, of course, the sought text must be within the first 254 characters

    array formula to be entered by pressing enter while holding down the ctrl and shift keys. It will automatically appear in curly braces in the formula bar.

    Best regards

    --
    The quality of the answer mainly depends on the clarity of the question, thank you!
    1
  2. Kahelm Posted messages 12 Registration date   Status Member Last intervention   1
     
    Thank you very much, it works!!

    A big thank you for your knowledge. :-)
    1
  3. Gyrus Posted messages 3360 Status Member 526
     
    Hello,

    A priori, if the search returns #N/A, it's mostly because the first word of C2 hasn't been found in any cell in column D.

    Can you share an example of your workbook?

    See you!
    0
  4. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    I don’t think anyone can respond to you without at least having an overview of the texts being processed, especially those you put in C2... because that’s where the problem should come from
    For now, all we can conclude from your data is that C2 is not included in your data when the word does not contain a space. (So action of the second item)
    But to say more, we would need to know more
    If you want, please upload an example of your problem with some texts (including some that do not work) at:
    https://www.cjoint.com/
    and come back here to paste the link created on the site
    Looking forward to hearing from you?
    Best regards

    --
    The quality of the response mainly depends on the clarity of the question, thank you!
    0
  6. Kahelm Posted messages 12 Registration date   Status Member Last intervention   1
     


    An example, it doesn't work because the cell in D containing Foot is too long, however it works if we remove some characters:



    The function is in column G, in G4, it remains the result #N/A because Gnagna has not been found in column D.

    But we can clearly see that Foot is identified, but when the cell containing foot has a large number of characters, vlookup can't find it anymore, it's strange?
    0