vlookup formula using wild cards & mid()

[thetrad9]

hello,
I needed to do a text based vlookup on two country fields. The issues were that sometimes the countries were not spelt the same in either sheet e.g. in sheet A = Taiwan, sheet B = Taiwan, province of China.

I eventually came up with a formula that works but it’s pretty big and unwieldy. I’m trying to find a better/more elegant way to do this.

=IF(IFERROR(MID(A9,1,FIND(",",A9)-1),"No find") = "No find", VLOOKUP("*" & [@[ country_name]] & "*",'WIZ Population'!A$1:BG$249,59,FALSE),VLOOKUP("*" & MID([@[ country_name]],1,FIND(",",[@[ country_name]])-1) & "*",'WIZ Population'!A$1:BG$249,59,FALSE))

Basically my formula:

• checks the reference cell for the existence of a “,” e.g. Taiwan, province of China, then uses
• MID(A2,1,FIND(",",A2)-1),"No find") = "No find" to return all characters before the “,” in this case just Taiwan.
• the overarching if statement then uses 2 separate Vlookup formulas depending on whether the iferror() function returned “no find” or not for the presence of a comma.

Any suggestions would be greatly appreciated.
Thanks

 

[vicedo]

You can complete the formula below

=IF(ISERROR(SEARCH(",",A9)),"No find",VLOOKUP(LEFT(L10,FIND(",",L10)-1),"input need range","number",FALSE))

OR

provide a sample spreadsheet