Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 408K Members
- 7,537 Online
- 465K Conversations

- Home
- :
- Excel
- :
- General Discussion
- :
- Formulas to add info to cell based on a WORD in another cell

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

SOLVED
Home
## Formulas to add info to cell based on a WORD in another cell

- Home
- :
- Excel
- :
- General Discussion
- :
- Formulas to add info to cell based on a WORD in another cell

Conversation Options

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2019 08:11 AM

I'm looking to get a cell to add information based on another cell, and can't seem to get it...

Basically, what I want to do is have a cell look at another, and based on a specific word, add specific information. For example:

If cell A2 has the word 'MB' in it, then it should add '192.104.'

If cell A2 has the word 'SK' in it, then it should add '192.103.'

If cell A2 has the word 'AB' in it, then it should add '192.102.'

If cell A2 has the word 'BC' in it, then it should add '192.101.'

I've tried some IF statements, and they hit up the #NAME? instead. I've even tried to get it to look ONLY at one, and add in one set. Here's what I've tried:

=IF(A2:A500=MB,"192.104.",IF(A2:A500=AB,"192.102.",IF(A2:A500=SK,"192.103.",IF(A2:A500=BC,"192.101.", "ERROR"))))

Is there something I am missing, or a better way to get the information I want?

Eventually, I'm also looking to have it query previous cells for the last part and add in the next.

Is this even possible in Excel?

Labels:

9 Replies

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2019 08:25 AM

It would be much easier to visualize what exactly you are trying to achieve if you upload a small sample file along with the desired output mocked up manually and an explanation which justify your desired output in the next column for few rows.

Btw #NAME? error simply means that Excel doesn't recognize the formula you are using or an incorrect formula e.g. in your formula you need to surround the words with double quotes like "MB", "AB" etc.

And if you miss those double quotes Excel assumes MB, AB etc are some functions which it doesn't recognize and produces that error.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2019 08:59 AM

Thanks for the reply!

Basically, this is what it would look like:

Cell A has a list of Province Acronyms, Cells B through E don't mean anything to this cell (They're their own thing.

I'm building out hundreds of these things, and they have IP addresses based on their province, and eventually, there may be other's that will build as well, and we need to know what addresses we are using so that there's no errors or overlap.

Basically, the cells in F should look to see what acronym is in cell A, and add in the correct first half of the IP range. The F column will eventually be hidden, and the last half will be added in manually based on the previous ranges for that same province, producing the full IP address. Similar things will occur for an additional five IP's per device based on other factors that will grab from F column and others.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2019 09:22 AM

Thanks for the image, though images are not very helpful and difficult to work with.

I requested you to upload a sample Excel file not the image along with the desired output mocked up manually. Please do so if possible.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2019 09:44 AM

Ahh okay. Here is basically what I am doing. I have a section highlighted in green that is what I'll be slowly building in to get our finished products and all the info.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2019 10:17 AM - edited 09-26-2019 10:20 AM

Thanks for the sample file.

It is still not clear to me that which column you want to populate with the formula depending on the province in column A.

If I read your original description which is as below...

If cell A2 has the word 'MB' in it, then it should add '192.104.'

If cell A2 has the word 'SK' in it, then it should add '192.103.'

If cell A2 has the word 'AB' in it, then it should add '192.102.'

If cell A2 has the word 'BC' in it, then it should add '192.101.'

All I understand is, the formula should look at the province in column A and add the network path (e.g. if province is MB, add 192.104.) but the question is, add 192.104. to what?

Can you explain your desired output for the first row and let me know if the province in A2 is AB and F2 is 192.102., which column you would be populating based on these two values?

Also, how many provinces you may have in column A? It would be easy to construct the formula if you create a Lookup table somewhere on the sheet or on another sheet to get what network path you need to add as per the province present in column A to avoid a long nesting IF formula.

OR

You want to populate the column F based on the province in column A?

e.g.

If cell A2 has the word 'AB' in it, then populate F2 with 192.102.?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2019 10:28 AM

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**

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2019 11:13 AM

SolutionSo 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",""))`

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2019 11:21 AM

Fascinating! Thank you very much!

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-26-2019 11:28 AM

Related Conversations

Stable version of Edge insider browser

HotCakeX
in
Discussions
on
10-12-2019
21.8K
Views

1 Likes

35 Replies

flashing a white screen while open new tab

cntvertex
in
Discussions
on
10-05-2019
20.9K
Views

9 Likes

11 Replies

How to Prevent Teams from Auto-Launch

chenrylee
in
Microsoft Teams
on
06-27-2019
133K
Views

6 Likes

28 Replies

What is Canary ring in Windows insider program? and how do we get them?

HotCakeX
in
Windows Insider Program
on
09-27-2019
10.9K
Views

0 Likes

9 Replies

How to download windows server 2019 update to 1903

Cmakar37
in
Windows Server for IT Pro
on
07-03-2019
21.9K
Views

0 Likes

7 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft