If and Vlookup with Multiple Criteria

[kumara_faith]

Hi,

I have a list of data where the letters starts with the list in the table below in column Category. I need to insert a formula to return as the column "Then". I tried If and LEFT but I am not sure to cater for the long list .
Is there a way have a formula for this ? Appreciate all the help.

Table 1

Excel Workbook
	B	C
2	Category	Then
3	RF	Police
4	I	Police
5	G	Police
6	M	Old IC
7	A	Old IC
8	A plus any alphabet	BC
9	E	BC
10	J	BC
11	B	BC
12	C	BC
13	F	BC
14	S	Singapore
15	T	Army
16	Dummy	Migration
17	K	BC
18	L	BC
19	N	Navy
20	P	Passport
21	Q	BC
22	U	Passport
23	X	Passport
24	Y	Passport
25	Z	Passport
Sheet1

Sample Data and The Correct Return Value

Excel Workbook
	E	F
2	Sample Data	Return As
3	RF1254	Police
4	I44775	Police
5	G4557	Police
6	M4555	Old IC
7	A4558	Old IC
8	AC11225	BC
9	E455578	BC
10	J4558	BC
11	B4585	BC
12	C55887	BC
13	F2554	BC
14	S22554	Singapore
15	T22554	Army
16	Dummy4558	Migration
17	K5558	BC
18	L55558	BC
19	N5558	Navy
20	P2255	Passport
21	Q55587	BC
22	U55587	Passport
23	X22554	Passport
24	Y5558	Passport
25	Z5558	Passport
Sheet1

 

[Aladin Akyurek]

Sort A:B on A in ascending order.

In F2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$24,$E2),$B$2:$B$24)

 

[Aladin Akyurek]

The following would take the presence of " plus any alphabet" into account...

=LOOKUP(9.99999999999999E+307,SEARCH(SUBSTITUTE($A$2:$A$24," plus any alphabet","?"),$E2),$B$2:$B$24)

 

[kumara_faith]

Hi Aladin,

I am not sure if I understood you correctly. I have tried the formula but it does not return the correct values. My apologies if I am doing this wrongly.

Excel Workbook
	A	B	C	D	E	F
2		Category	Then		Sample Data	Return As
3	A	RF	Police		RF1254	N
4	A plus any alphabet	I	Police		I44775	B
5	B	G	Police		G4557	J
6	C	M	Old IC		M4555	T
7	Dummy	A	Old IC		A4558	I
8	E	A plus any alphabet	BC		AC11225	M
9	F	E	BC		E455578	A plus any alphabet
10	G	J	BC		J4558	C
11	I	B	BC		B4585	G
12	J	C	BC		C55887	M
13	K	F	BC		F2554	E
14	L	S	Singapore		S22554	P
15	M	T	Army		T22554	Q
16	N	Dummy	Migration		Dummy4558	Y
17	P	K	BC		K5558	F
18	Q	L	BC		L55558	S
19	RF	N	Navy		N5558	Dummy
20	S	P	Passport		P2255	K
21	T	Q	BC		Q55587	L
22	U	U	Passport		U55587	U
23	X	X	Passport		X22554	X
24	Y	Y	Passport		Y5558	Y
25	Z	Z	Passport		Z5558	Z
Sheet1

 

[AlanY]

this is how Aladin's post#2 works

Excel 2013/2016
	A	B	C	D	E	F
1					Sample Data	Return As
2	A	Old IC			RF1254	Police
3	A 123	BC			I44775	Police
4	B	BC			G4557	Police
5	C	BC			M4555	Old IC
6	Dummy	Migration			A4558	Old IC
7	E	BC			AC11225	BC
8	F	BC			E455578	BC
9	G	Police			J4558	BC
10	I	Police			B4585	BC
11	J	BC			C55887	BC
12	K	BC			F2554	BC
13	L	BC			S22554	Singapore
14	M	Old IC			T22554	Army
15	N	Navy			Dummy4558	Passport
16	P	Passport			K5558	BC
17	Q	BC			L55558	BC
18	RF	Police			N5558	Navy
19	S	Singapore			P2255	Passport
20	T	Army			Q55587	BC
21	U	Passport			U55587	Passport
22	X	Passport			X22554	Passport
23	Y	Passport			Y5558	Passport
24	Z	Passport			Z5558	Passport
Sheet1

Cell Formulas
Range	Formula
F2	=LOOKUP(9.99999999999999E+307,SEARCH($A$2:$A$24,$E2),$B$2:$B$24)

 

[AlanY]

.......... deleted

 

[AlanY]

unfortunately, it didn't work for the "Dummy" entry

Excel 2013/2016
	A	B	C	D	E	F
1					Sample Data	Return As
2	A	Old IC			RF1254	Police
3	AB	BC			I44775	Police
4	B	BC			G4557	Police
5	C	BC			M4555	Old IC
6	Dummy	Migration			A4558	Old IC
7	E	BC			AC11225	BC
8	F	BC			E455578	BC
9	G	Police			J4558	BC
10	I	Police			B4585	BC
11	J	BC			C55887	BC
12	K	BC			F2554	BC
13	L	BC			S22554	Singapore
14	M	Old IC			T22554	Army
15	N	Navy			Dummy4558	Passport
16	P	Passport			K5558	BC
17	Q	BC			L55558	BC
18	RF	Police			N5558	Navy
19	S	Singapore			P2255	Passport
20	T	Army			Q55587	BC
21	U	Passport			U55587	Passport
22	X	Passport			X22554	Passport
23	Y	Passport			Y5558	Passport
24	Z	Passport			Z5558	Passport
Sheet1

 

[Aladin Akyurek]

Hi Aladin,

I am not sure if I understood you correctly. I have tried the formula but it does not return the correct values. My apologies if I am doing this wrongly.

[...]

unfortunately, it didn't work for the "Dummy" entry

[...]

AlanY: Thanks for catching that.

In F2 enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(SUBSTITUTE("|"&$A$2:$A$24," plus any alphabet","?"),"|"&$E2),$B$2:$B$24)

Here is the workbook that implements the suggestion: https://www.dropbox.com/s/1t1dsuybj5xkj02/kumara_faith%20LOOKUP%2BSEARCH.xlsx?dl=0

 

[kumara_faith]

Hi AlanY and Aladin,

Thank you for the assistance. Appreciate it.

The following is the table from Aladin's worksheet. In the Category, if the data starts with A + any alphabets such as AC11225, then the formula should return as BC.But if the data only has A with numeric without any alphabet such as A4558 , then the formula should return as Old IC. In the table below, A4558 is returning as BC when it should return as Old IC. I am not sure how to rectify this. Appreciate the help and my apologies.

Excel Workbook
	A	B	C	D	E	F
1	Category	Then			Sample Data
2	A	Old IC			RF1254	Police
3	A plus any alphabet	BC			I44775	Police
4	B	BC			G4557	Police
5	C	BC			M4555	Old IC
6	Dummy	Migration			A4558	BC
7	E	BC			AC11225	BC
8	F	BC			E455578	BC
9	G	Police			J4558	BC
10	I	Police			B4585	BC
11	J	BC			C55887	BC
12	K	BC			F2554	BC
13	L	BC			S22554	Singapore
14	M	Old IC			T22554	Army
15	N	Navy			Dummy4558	Migration
16	P	Passport			K5558	BC
17	Q	BC			L55558	BC
18	RF	Police			N5558	Navy
19	S	Singapore			P2255	Passport
20	T	Army			Q55587	BC
21	U	Passport			U55587	Passport
22	X	Passport			X22554	Passport
23	Y	Passport			Y5558	Passport
24	Z	Passport			Z5558	Passport
25					Ad	BC
Sheet1

 

[Aladin Akyurek]

Control+shift+enter, not just enter, and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH("|"&IF(ISNUMBER(MID($E2,2,1)+0),$A$2:$A$24,SUBSTITUTE($A$2:$A$24," plus any alphabet","?")),"|"&$E2),$B$2:$B$24)