Forum Discussion
Formulas to add info to cell based on a WORD in another cell
So if all you need is to populate the column F based on province entered in column A, you may try something like this...
In F2
=IF(A2="","",IFERROR(VLOOKUP(A2,Subnet,2,0),""))I have inserted a new sheet called "Lookup" where you can list all the provinces and their subnet.
I have also created a dynamic Named Range called "Subnet" which is used in the formula above.
You may add more provinces and their details on Lookup Sheet and your formula will be updated automatically as per the province you enter in column A if that province exists on Lookup Sheet.
So if A2 is AB, the formula will return 192.102. in F2.
But if you want F2 to return 192.102.1.0 in F2 as you showed in the cell F8 (green cell), you may tweak the formula as...
=IF(A8="","",IFERROR(VLOOKUP(A8,Subnet,2,0)&"1.0",""))
Sorry, So column F should be blank until a province is added into column A. There's only four provinces that I'm doing this for. No idea if we'll ever expand into more or not.
Column G and H are literally a drag down to get the next corresponding numbers. No formulas required.
Columns I, J and K are all based on the information in column F
So if all you need is to populate the column F based on province entered in column A, you may try something like this...
In F2
=IF(A2="","",IFERROR(VLOOKUP(A2,Subnet,2,0),""))
I have inserted a new sheet called "Lookup" where you can list all the provinces and their subnet.
I have also created a dynamic Named Range called "Subnet" which is used in the formula above.
You may add more provinces and their details on Lookup Sheet and your formula will be updated automatically as per the province you enter in column A if that province exists on Lookup Sheet.
So if A2 is AB, the formula will return 192.102. in F2.
But if you want F2 to return 192.102.1.0 in F2 as you showed in the cell F8 (green cell), you may tweak the formula as...
=IF(A8="","",IFERROR(VLOOKUP(A8,Subnet,2,0)&"1.0",""))
- Fascinating! Thank you very much!