Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Vlookup after LEFT, TRIM and still receiving ERROR

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Vlookup after LEFT, TRIM and still receiving ERROR

    Good day new to this community. I've been trying to use the following formula to have data on the LEFT of the same CELL to clean it and use it to VLOOK up another sheet and brining in a code.

    Formula: =
    VLOOKUP(LEFT(H4,(FIND("V.",TRIM(SUBSTITUTE(H4,CHAR(160),CHAR(32))))-2)),'Vlookup Data'!B:C,2,FALSE)

    The Cell: contains the following( see below in BOLD and that's where im trying to get the AS FAUSTINA from and do Vlookup from another table.

    AS FAUSTINA V. 22

    -------Tried to include small Sample excel sheet with but didn't see upload feature."

  2. #2
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Vlookup after LEFT, TRIM and still receiving ERROR

    Hi, your formula is working for me and giving AS FAUSTINA. Can you check if the value you are getting from the LEFT function is equal to AS FAUSTINA in Vlookup Data tab of the excel ?

    Also try below:

    Code:
    =VLOOKUP(LEFT(H4,SEARCH("/",SUBSTITUTE(H4," ","/",2))-1),'Vlookup Data'!B:C,2,FALSE)
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup after LEFT, TRIM and still receiving ERROR

    Quote Originally Posted by Aryatect View Post
    Hi, your formula is working for me and giving AS FAUSTINA. Can you check if the value you are getting from the LEFT function is equal to AS FAUSTINA in Vlookup Data tab of the excel ?

    Also try below:

    Code:
    =VLOOKUP(LEFT(H4,SEARCH("/",SUBSTITUTE(H4," ","/",2))-1),'Vlookup Data'!B:C,2,FALSE)
    ----------------------- @Aryatect Thank you for your feedback yes the formula works fine when its text. Thats what i wanted to include my sample sheet as the cell was pulled from a database and appears to have funky type of spacing. How can I share/upload my small sheet with the sample for you to look at?

  4. #4
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Vlookup after LEFT, TRIM and still receiving ERROR

    You can use dropbox or googledrive to upload your file and share the link here.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  5. #5
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup after LEFT, TRIM and still receiving ERROR


  6. #6
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Vlookup after LEFT, TRIM and still receiving ERROR

    Hi, So this you have Char(160) in the first tab but second tab has CHAR(32) - "space" that is why it is not matching, below should work:

    Code:
    =VLOOKUP(SUBSTITUTE(LEFT(G4,SEARCH("/",SUBSTITUTE(G4,CHAR(160),"/",2))-1),CHAR(160)," "),'Vlookup Data'!B:C,2,FALSE)
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  7. #7
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Vlookup after LEFT, TRIM and still receiving ERROR

    Hi,

    Formula in Sheet 1 I4 copied down:

    GHI
    4AS FAUSTINA                   V.     22AFA
    5AS FAUSTINA                   V.     22AFA
    6AS FAUSTINA                   V.     22AFA

    Sheet1



    Worksheet Formulas
    CellFormula
    I4=VLOOKUP(TRIM(LEFT(SUBSTITUTE(G4,CHAR(160)," "),SEARCH("V.",G4)-1)),'Vlookup Data'!B:C,2,0)


  8. #8
    New Member
    Join Date
    Jul 2019
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Vlookup after LEFT, TRIM and still receiving ERROR

    Thank you very much this worked like a charm. I now need to review your MAGIC formula to understand for future. But greatly appreciate your time and help. Cheers

  9. #9
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    308
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Vlookup after LEFT, TRIM and still receiving ERROR

    Glad we could help and thanks for the feedback.

    Basically we are swapping the CHAR(160) with CHAR(32) which you did too, I was just searching for the second "space" and calculated from there and jtakw used TRIM after getting the data.
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  10. #10
    Board Regular
    Join Date
    Jun 2014
    Location
    Oakland, CA
    Posts
    5,146
    Post Thanks / Like
    Mentioned
    42 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Vlookup after LEFT, TRIM and still receiving ERROR

    Not sure who's formula you're referring to, but just want to point out, formula posted in Post # 6 will fail for Single Word or more than 2 word Vessel names since it relies on the 2nd CHAR(160), whereas my formula in Post # 7 will work for Any number of words for Vessel names.

    Anyhow, you're welcome, and welcome to the forum.

    GHIJ
    4AS FAUSTINA                   V.     22AFAAFA
    5Acapulco                   V.     22ACA#N/A
    6AS FAUSTINA                   V.     22AFAAFA
    7My formulaPost #6

    Sheet1



    Worksheet Formulas
    CellFormula
    I4=VLOOKUP(TRIM(LEFT(SUBSTITUTE(G4,CHAR(160)," "),SEARCH("V.",G4)-1)),'Vlookup Data'!B:C,2,0)
    J4=VLOOKUP(SUBSTITUTE(LEFT(G4,SEARCH("/",SUBSTITUTE(G4,CHAR(160),"/",2))-1),CHAR(160)," "),'Vlookup Data'!B:C,2,FALSE)

    Last edited by jtakw; Jul 19th, 2019 at 05:27 PM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •