Lookup or If/Find

[DIE_ARD]

HI All,
First post in many years. please be gentle.
Im not sure the correct solution for this one, ideally, I need to do this without having to use a lookup table as doing it in Power Query and cant work out the formula for it.

Please see data set (Col D), this can be up to 160k rows
Anything beginning with C in the data col I want to show as Corporate
Anything beginning with T in the data col I want to show as Technical
Anything that shows as 00098 I want to show as Cab1
Etc etc as per the lookup table on the left.
Any suggestions would be gratefully received.

Thanks.

Paul.

 

[ExceLoki]

using only the given lookup values, the below formula could work
--------------

Book1
	D	E
2	data	result
3	99	Cab2
4	98	Cab1
5	c12345	corporate
6	m1234	unknown
7	m3456	unknown
8	c0598	corporate
9	r9968	shop
10	c1237	corporate
11	c98787	corporate
Sheet1

Cell Formulas
Range	Formula
E3:E11	E3	=IFS(ISNUMBER(D3),IF(D3=99,"Cab2","Cab1"),LEFT(D3,1)="c","corporate",LEFT(D3,1)="t","technical",LEFT(D3,1)="m","unknown",LEFT(D3,1)="r","shop")

 

[DIE_ARD]

using only the given lookup values, the below formula could work
--------------

Book1
	D	E
2	data	result
3	99	Cab2
4	98	Cab1
5	c12345	corporate
6	m1234	unknown
7	m3456	unknown
8	c0598	corporate
9	r9968	shop
10	c1237	corporate
11	c98787	corporate
Sheet1

Cell Formulas
Range	Formula
E3:E11	E3	=IFS(ISNUMBER(D3),IF(D3=99,"Cab2","Cab1"),LEFT(D3,1)="c","corporate",LEFT(D3,1)="t","technical",LEFT(D3,1)="m","unknown",LEFT(D3,1)="r","shop")

Hi
Thanks very much that, it worked perfect.
I can add lookup values to the formula as I go on.
Thanks for taking the time to look at this and replying, I really appreciate it.

 

[ExceLoki]

glad to help. please consider marking it as solution if it is fact a solution for your post.

 

[smozgur]

Thanks very much that, it worked perfect.

The marked solution post has been switched to the actual answer to the question.

@DIE_ARD: In your future questions, please mark the post as the solution that answered your question to help future readers. No further action is required in this thread.