Vlookup & Search - Multiple values to look up and enter

[Steve2020]

Hi,

This is the formula I am currently using in the top right most cell in the table below showing "Melbourne":

(Look at the 3rd column and see if it contains "MEL", if it does then return value in column 2)

=VLOOKUP(MID(D2,SEARCH("MEL",D2),3),$B$2:$C$9,2,FALSE)

(Lookup table is the first 2 columns, 3rd column is the value lookedup, 4th column contains the formula above):

ADL Adelaide zzzzzz_MEL_jjjjj_1000 Melbourne BNE Brisbane zzzzzz_MEL_jjjjj_1000 Melbourne CAIRNS Cairns zzzzzz_PER_jjjjj_1000 #VALUE! CBR Canberra zzzzzz_PER_jjjjj_1000 #VALUE! DRW Darwin zzzzzz_SYD_jjjjj_1000 #VALUE! MEL Melbourne zzzzzz_SYD_jjjjj_1000 #VALUE! PER Perth yyy_ADL_jjjjj_1000 #VALUE! SYD Sydney yyy_ADL_jjjjj_1000 #VALUE! <tbody> </tbody>

<tbody>
</tbody>

What I'd like to do is have the Search function include PER, SYD, etc. so that it will look up the rest of the cities, and not just MEL which is the current state of the formula.

Would anyone be able to help out here please?

 

[NiMip]

Someone may have a better way, but I'll share my solution anyway! It uses INDEX rather than VLOOKUP.
If you number each of your search criteria as I have below, you can then use the following formula starting in cell F2 and filling down to do what you're after. I've added a couple more rows to show the lookup table (B:D) is separate to the data table (E:F):
{=INDEX($D$2:$D$9,MAX(IF(ISNUMBER(SEARCH($C$2:$C$9,E2)),$B$2:$B$9)))}

Note this is an array formula and requires control+shift+enter to work

	A	B	C	D	E	F	G
1
2		1	ADL	Adelaide	zzzzzz_MEL_jjjjj_1000	Melbourne
3		2	BNE	Brisbane	zzzzzz_MEL_jjjjj_1000	Melbourne
4		3	CAIRNS	Cairns	zzzzzz_PER_jjjjj_1000	Perth
5		4	CBR	Canberra	zzzzzz_PER_jjjjj_1000	Perth
6		5	DRW	Darwin	zzzzzz_SYD_jjjjj_1000	Sydney
7		6	MEL	Melbourne	zzzzzz_SYD_jjjjj_1000	Sydney
8		7	PER	Perth	yyy_ADL_jjjjj_1000	Adelaide
9		8	SYD	Sydney	yyy_ADL_jjjjj_1000	Adelaide
10					yyy_ADL_jjjjj_1000	Adelaide
11					yyy_SYD_jjjjj_1000	Sydney
12					yyy_CAIRNS_jjjjj_1000	Cairns
13					yyy_ADL_jjjjj_1000	Adelaide
14

<tbody>
</tbody>

 

[Aladin Akyurek]

In D1 enter and copy down:

=IFERROR(LOOKUP(9.99999999999999E+307,SEARCH("_"&$A$1:$A$8&"_",$C1),$B$1:$B$8),"")