Vlookup after LEFT, TRIM and still receiving ERROR
Page 2 of 2 FirstFirst 12
Results 11 to 20 of 20

Thread: Vlookup after LEFT, TRIM and still receiving ERROR

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

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

    Yup that is correct @jtakw, I assumed ad space in between, yours is more robust formula.
    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

  2. #12
    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 jtakw View Post
    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.

    G H I J
    4 AS FAUSTINA V. 22 AFA AFA
    5 Acapulco V. 22 ACA #N/A
    6 AS FAUSTINA V. 22 AFA AFA
    7 My formula Post #6
    Sheet1

    Worksheet Formulas
    Cell Formula
    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)
    Hello everyone i tried to apply the I4 formula to this problem but cant seem to get it. I have provided a Link for your ease of review to this issue:

    https://www.dropbox.com/s/fxob5p0ofp...ZZZZ.xlsx?dl=0

    The issue is I cant Vlookup Column C from Array B:C with Lookup Value Column E

    Cheers.

  3. #13
    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=StickyNote1;5315289]
    @jtakw, @Aryatect

    Hello I tried to apply the I4 formula to this problem but cant seem to get it. I have provided a Link for your ease of review to this issue:


    https://www.dropbox.com/s/fxob5p0ofp...ZZZZ.xlsx?dl=0

    The issue is I cant Vlookup Column C from Array B:C with Lookup Value Column E it generates the wonderful #N/A

    Cheers. Any help is appreciated.

  4. #14
    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

    @jtakw, @Aryatect

    Hello I tried to apply the I4 formula to this problem but cant seem to get it. I have provided a Link for your ease of review to this issue:


    https://www.dropbox.com/s/fxob5p0ofp...ZZZZ.xlsx?dl=0

    The issue is I cant Vlookup Column C from Array B:C with Lookup Value Column E it generates the wonderful #N/A

    Cheers. Any help is appreciated.

  5. #15
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    301
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    Hi, This is a different question from the previous one, there you were looking for the text left of "V." which had CHAR(160). Here you have everything same, except CHAR(160) as an invisible character in the lookup value and CHAR(32) or space in the table array. In this case below formula in Cell G4 and copying it down will work:

    Code:
    =VLOOKUP(SUBSTITUTE(E7,CHAR(160)," "),$B$7:$C$10,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

  6. #16
    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

    Aryatect,

    Your suggested formula worked great, thank you. I do have a question how you usually know when a cell has special characters to applie CHAR 160 or 62? Just by trial and error or do you have another process?

  7. #17
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    301
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    Glad could help and thanks for the feed back. I usually look for each character like below:

    BCDEFGHIJKLMNOPQRST
    7CHARTER HIRE-LEASED
    1412345678910111213141516171819
    15CHARTER HIRE-LEASED
    16677265828469821607273826945766965836968

    Sheet1



    Worksheet Formulas
    CellFormula
    B15=MID($E$7,B14,1)
    B16=CODE(B15)

    Last edited by Aryatect; Jul 26th, 2019 at 02:45 PM.
    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

  8. #18
    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, This is a different question from the previous one, there you were looking for the text left of "V." which had CHAR(160). Here you have everything same, except CHAR(160) as an invisible character in the lookup value and CHAR(32) or space in the table array. In this case below formula in Cell G4 and copying it down will work:

    Code:
    =VLOOKUP(SUBSTITUTE(E7,CHAR(160)," "),$B$7:$C$10,2,FALSE)
    Aryatect,

    Your suggested formula worked great, thank you. I do have a question how you usually know when a cell has special characters to applie CHAR 160 or 62? Just by trial and error or do you have another process?

  9. #19
    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

    Understood I see; thanks again and have a good weekend.

  10. #20
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    301
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

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

    Glad to help and you have a great weekend too!
    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

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
  •