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):
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 615"]
<tbody>[TR="class: grid"]
[TD]ADL[/TD]
[TD]Adelaide[/TD]
[TD]zzzzzz_MEL_jjjjj_1000[/TD]
[TD]Melbourne[/TD]
[/TR]
[TR="class: grid"]
[TD]BNE[/TD]
[TD]Brisbane[/TD]
[TD]zzzzzz_MEL_jjjjj_1000[/TD]
[TD]Melbourne[/TD]
[/TR]
[TR="class: grid"]
[TD]CAIRNS[/TD]
[TD]Cairns[/TD]
[TD]zzzzzz_PER_jjjjj_1000[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR="class: grid"]
[TD]CBR[/TD]
[TD]Canberra[/TD]
[TD]zzzzzz_PER_jjjjj_1000[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR="class: grid"]
[TD]DRW[/TD]
[TD]Darwin[/TD]
[TD]zzzzzz_SYD_jjjjj_1000[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR="class: grid"]
[TD]MEL[/TD]
[TD]Melbourne[/TD]
[TD]zzzzzz_SYD_jjjjj_1000[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR="class: grid"]
[TD]PER[/TD]
[TD]Perth[/TD]
[TD]yyy_ADL_jjjjj_1000[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
[TR="class: grid"]
[TD]SYD[/TD]
[TD]Sydney[/TD]
[TD]yyy_ADL_jjjjj_1000[/TD]
[TD="align: center"]#VALUE![/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="class: grid, width: 408"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD]ADL[/TD]
[TD]Adelaide[/TD]
[TD]zzzzzz_MEL_jjjjj_1000[/TD]
[TD]Melbourne[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD]BNE[/TD]
[TD]Brisbane[/TD]
[TD]zzzzzz_MEL_jjjjj_1000[/TD]
[TD]Melbourne[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD]CAIRNS[/TD]
[TD]Cairns[/TD]
[TD]zzzzzz_PER_jjjjj_1000[/TD]
[TD]Perth[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD]CBR[/TD]
[TD]Canberra[/TD]
[TD]zzzzzz_PER_jjjjj_1000[/TD]
[TD]Perth[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD]DRW[/TD]
[TD]Darwin[/TD]
[TD]zzzzzz_SYD_jjjjj_1000[/TD]
[TD]Sydney[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]6[/TD]
[TD]MEL[/TD]
[TD]Melbourne[/TD]
[TD]zzzzzz_SYD_jjjjj_1000[/TD]
[TD]Sydney[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"]7[/TD]
[TD]PER[/TD]
[TD]Perth[/TD]
[TD]yyy_ADL_jjjjj_1000[/TD]
[TD]Adelaide[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD]SYD[/TD]
[TD]Sydney[/TD]
[TD]yyy_ADL_jjjjj_1000[/TD]
[TD]Adelaide[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yyy_ADL_jjjjj_1000[/TD]
[TD]Adelaide[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yyy_SYD_jjjjj_1000[/TD]
[TD]Sydney[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yyy_CAIRNS_jjjjj_1000[/TD]
[TD]Cairns[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]yyy_ADL_jjjjj_1000[/TD]
[TD]Adelaide[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aladin Akyurek]

In D1 enter and copy down:

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