Vlookup after LEFT, TRIM and still receiving ERROR

[StickyNote1]

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.

[TABLE="width: 287"]
<tbody>[TR]
[TD="class: xl67, width: 287"]AS FAUSTINA V. 22[/TD]
[/TR]
</tbody>[/TABLE]

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

 

[Aryatect]

Yup that is correct @jtakw, I assumed ad space in between, yours is more robust formula.

 

[StickyNote1]

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
AFA	AFA
Acapulco V. 22	ACA
AFA	AFA
My formula	Post #6

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]4[/TD]
[TD="bgcolor: #D0D0D0"]AS FAUSTINA V. 22[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"][/TD]

[TD="align: right"]#N/A[/TD]

[TD="align: center"]6[/TD]
[TD="bgcolor: #D0D0D0"]AS FAUSTINA V. 22[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1 ​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]I4[/TH]
[TD="align: left"]=VLOOKUP(TRIM(LEFT(SUBSTITUTE(G4,CHAR(160)," "),SEARCH("V.",G4)-1)),'Vlookup Data'!B:C,2,0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]J4[/TH]
[TD="align: left"]=VLOOKUP(SUBSTITUTE(LEFT(G4,SEARCH("/",SUBSTITUTE(G4,CHAR(160),"/",2))-1),CHAR(160)," "),'Vlookup Data'!B:C,2,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[StickyNote1]

@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/fxob5p0ofprl85f/ZZZZZZZZZZZZZ.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.

 

[StickyNote1]

@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.

 

[Aryatect]

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:

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

 

[StickyNote1]

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]

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

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
7				CHARTERHIRE-LEASED
14	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19
15	C	H	A	R	T	E	R		H	I	R	E	-	L	E	A	S	E	D
16	67	72	65	82	84	69	82	160	72	73	82	69	45	76	69	65	83	69	68
Sheet1

Cell Formulas
Range	Formula
B15	=MID($E$7,B14,1)
B16	=CODE(B15)

 

[StickyNote1]

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:

=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?

 

[StickyNote1]

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

 

[Aryatect]

Glad to help and you have a great weekend too!