Vlookup after LEFT, TRIM and still receiving ERROR

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

StickyNote1

New Member
Joined
Jul 19, 2019
Messages
10
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

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
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)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
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/fxob5p0ofprl85f/ZZZZZZZZZZZZZ.xlsx?dl=0

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

Cheers.
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
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)
 

StickyNote1

New Member
Joined
Jul 19, 2019
Messages
10
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?
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Glad could help and thanks for the feed back. I usually look for each character like below:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="font-weight: bold;border-top: 1px solid black;border-right: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;;"></td><td style="font-weight: bold;text-align: right;border-top: 1px solid black;border-bottom: 1px solid black;border-left: 1px solid black;color: #0000FF;;"></td><td style="text-align: right;;"></td><td style=";">CHARTER HIRE-LEASED</td><td style="text-align: right;;"></td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;">1</td><td style="text-align: right;;">2</td><td style="text-align: right;;">3</td><td style="text-align: right;;">4</td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">7</td><td style="text-align: right;;">8</td><td style="text-align: right;;">9</td><td style="text-align: right;;">10</td><td style="text-align: right;;">11</td><td style="text-align: right;;">12</td><td style="text-align: right;;">13</td><td style="text-align: right;;">14</td><td style="text-align: right;;">15</td><td style="text-align: right;;">16</td><td style="text-align: right;;">17</td><td style="text-align: right;;">18</td><td style="text-align: right;;">19</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">C</td><td style=";">H</td><td style=";">A</td><td style=";">R</td><td style=";">T</td><td style=";">E</td><td style=";">R</td><td style=";"> </td><td style=";">H</td><td style=";">I</td><td style=";">R</td><td style=";">E</td><td style=";">-</td><td style=";">L</td><td style=";">E</td><td style=";">A</td><td style=";">S</td><td style=";">E</td><td style=";">D</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;">67</td><td style="text-align: right;;">72</td><td style="text-align: right;;">65</td><td style="text-align: right;;">82</td><td style="text-align: right;;">84</td><td style="text-align: right;;">69</td><td style="text-align: right;;">82</td><td style="text-align: right;;">160</td><td style="text-align: right;;">72</td><td style="text-align: right;;">73</td><td style="text-align: right;;">82</td><td style="text-align: right;;">69</td><td style="text-align: right;;">45</td><td style="text-align: right;;">76</td><td style="text-align: right;;">69</td><td style="text-align: right;;">65</td><td style="text-align: right;;">83</td><td style="text-align: right;;">69</td><td style="text-align: right;;">68</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B15</th><td style="text-align:left">=MID(<font color="Blue">$E$7,B14,1</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B16</th><td style="text-align:left">=CODE(<font color="Blue">B15</font>)</td></tr></tbody></table></td></tr></table><br />
 
Last edited:

StickyNote1

New Member
Joined
Jul 19, 2019
Messages
10
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,263
Messages
5,485,744
Members
407,512
Latest member
PearceK

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top