Feb 17 2022 01:55 PM
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 |
Feb 17 2022 03:21 PM
Solution=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.
Feb 18 2022 02:15 AM
Feb 18 2022 05:35 AM
=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.
Feb 17 2022 03:21 PM
Solution=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.