SOLVED

New Contributor

# Location name if the cell begins with different text

Hi, I have a excel sheet with 50000 rows of global data. I want to populate the Country by checking Name column if it begins with ABC1GB then Country is UK, if begins with ABC1US then Country is America, if beings with ABC1AU then Country is Australia, if beings with ABC1IN then Country is India. I want to populate Location by checking if the IP is between 10.224.5.1 and 10.224.5.254 then Location is London and so on. Please suggest the best approach to achieve this.

 Name IP Address Country Location ABC1GBM 10.224.5.7 ABC1USM 10.227.8.9 ABC1AUM 10.226.5.4 ABC1INM 10.221.4.3
3 Replies
best response confirmed by excelinLife (New Contributor)
Solution

# Re: Location name if the cell begins with different text

``=VLOOKUP(LEFT(A2,6),\$F\$2:\$G\$13,2,FALSE)``

This is the formula in cell C2.

``=IF(AND(NOT(ISNA(VLOOKUP(LEFT(B2,9),\$J\$2:\$K\$13,2,FALSE))),AND(NUMBERVALUE(RIGHT(B2,LEN(B2)-9))>=1,NUMBERVALUE(RIGHT(B2,LEN(B2)-9))<=254)),VLOOKUP(LEFT(A2,6),\$F\$2:\$H\$13,3,FALSE),"")``

This is the formula in cell D2.

With these formulas you can easily add more criteria.

# Re: Location name if the cell begins with different text

Thank you could you please share the formula you shared yesterday without vlookup that suited the requirement perfectly, sorry.

# Re: Location name if the cell begins with different text

``=IF(LEFT(A4,6)="ABC1GB","UK",IF(LEFT(A4,6)="ABC1US","USA",IF(LEFT(A4,6)="ABC1AU","Australia",IF(LEFT(A4,6)="ABC1IN","India",""))))``

This is the formula in cell C2.

``=IF(AND(LEFT(B4,9)="10.224.5.",AND(NUMBERVALUE(RIGHT(B4,LEN(B4)-9))>=1,NUMBERVALUE(RIGHT(B4,LEN(B4)-9))<=254)),"London",IF(AND(LEFT(B4,9)="10.227.8.",AND(NUMBERVALUE(RIGHT(B4,LEN(B4)-9))>=1,NUMBERVALUE(RIGHT(B4,LEN(B4)-9))<=254)),"Washington",IF(AND(LEFT(B4,9)="10.226.5.",AND(NUMBERVALUE(RIGHT(B4,LEN(B4)-9))>=1,NUMBERVALUE(RIGHT(B4,LEN(B4)-9))<=254)),"Canberra",IF(AND(LEFT(B4,9)="10.221.4.",AND(NUMBERVALUE(RIGHT(B4,LEN(B4)-9))>=1,NUMBERVALUE(RIGHT(B4,LEN(B4)-9))<=254)),"New-Dehli",""))))``

This is the formula in cell D2.