Mix of Vlookup, find and substitute

[Cooki]

Hi All

Have issues with this formula

=VLOOKUP(FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".","")))),Data!B:C,2,0)

What i want is to do a Vlookup on everything in the cell up to the full STOP (.).

This formula is giving me the position of the last full stop

FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".",""))))

but now i want to include vlookup everything up to the full stop

Any ideas?

 

[raj08536]

Can you share screenshot?

 

[Dave Patton]

try Add your Lookup formula to
=LEFT(A8,FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".",""))))-1)

 

[Cooki]

Changed formula just for the screen shot

=VLOOKUP(FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".","")))),$A$16:$B$20,2,0)

 

[Cooki]

try Add your Lookup formula to
=LEFT(A8,FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".",""))))-1)

No still get N/A

 

[Dave Patton]

You left out part of the suggestion.
Try using Excel's Formula Evaluate.
The info below also shows an alternative
I just read your message again. You may not need the minus 1 since your Table has a decimal at the end.

T202007b.xlsm
	A	B	C	D	E
8	abc.def.a	99	8		99
5c

Cell Formulas
Range	Formula
B8	B8	=VLOOKUP(LEFT(A8,FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".",""))))-1),A16:B20,2,0)
C8	C8	=FIND("~",SUBSTITUTE(A8,".","~",2))
E8	E8	=VLOOKUP(LEFT(A8,FIND("~",SUBSTITUTE(A8,".","~",2))-1),A16:B20,2,0)

T202007b.xlsm
	A	B	C	D	E	F
8	abc.def.a	99			99
5c

Cell Formulas
Range	Formula
B8	B8	=VLOOKUP(LEFT(A8,FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".",""))))),A16:B20,2,0)
E8	E8	=VLOOKUP(LEFT(A8,FIND("~",SUBSTITUTE(A8,".","~",2))),A16:B20,2,0)

 

[Dave Patton]

My suggestions assumed that there were 2 "." and you wanted to stop at the last "."

To post a clear example of your challenge with information that we can use, see the link to XL2BB.

T202007b.xlsm
	A	B	C	D	E
8	HT/HP Report:HHDC.XXXXXXX	99	99	18	99
5c

Cell Formulas
Range	Formula
B8	B8	=VLOOKUP(LEFT(A8,FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".",""))))),A16:B20,2,0)
C8	C8	=VLOOKUP(LEFT(A8,FIND("~",SUBSTITUTE(A8,".","~",1))),A16:B20,2,0)
D8	D8	=FIND(".",A8,1)
E8	E8	=VLOOKUP(LEFT(A8,FIND(".",A8,1)),A16:B20,2,0)

 

[Cooki]

You left out part of the suggestion.
Try using Excel's Formula Evaluate.
The info below also shows an alternative
I just read your message again. You may not need the minus 1 since your Table has a decimal at the end.

T202007b.xlsm
	A	B	C	D	E
8	abc.def.a	99	8		99
5c

Cell Formulas
Range	Formula
B8	B8	=VLOOKUP(LEFT(A8,FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".",""))))-1),A16:B20,2,0)
C8	C8	=FIND("~",SUBSTITUTE(A8,".","~",2))
E8	E8	=VLOOKUP(LEFT(A8,FIND("~",SUBSTITUTE(A8,".","~",2))-1),A16:B20,2,0)

T202007b.xlsm
	A	B	C	D	E	F
8	abc.def.a	99			99
5c

Cell Formulas
Range	Formula
B8	B8	=VLOOKUP(LEFT(A8,FIND("*",SUBSTITUTE(A8,".","*",LEN(A8)-LEN(SUBSTITUTE(A8,".",""))))),A16:B20,2,0)
E8	E8	=VLOOKUP(LEFT(A8,FIND("~",SUBSTITUTE(A8,".","~",2))),A16:B20,2,0)

Worked prefect thanks