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

 

[kumara_faith]

Hi Aladin,

Thank you very much. That worked. Appreciate your time and patience.

If it is not too much trouble, may I know how this formula actually works ?

 

[Aladin Akyurek]

Hi Aladin,

Thank you very much. That worked. Appreciate your time and patience.

If it is not too much trouble, may I know how this formula actually works ?

You are welcome.

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)

Here is a generic explanation for this type of formulas: https://www.mrexcel.com/forum/excel-questions/724843-there-case-sensitive-vlookup.html

The formula can be re-written in abstract terms as:

lookup(bignum,search-array,result-array)

The search-array here has an if-filter:

If the 2nd char of target string is a number, use the A2:A24 as is as search value, otherwise substitute in A2:A24 the wildcard ? for " plus an alphabet. Note that "|" is used to locate the symbols of A2:A24 at the start of target string in E2.

Hope this helps.