If a cell contains a word, enter another word in different cell

[walby]

Hello,

I am learning a lot about excel as I try to make a budget worksheet. I have a column containing drop down list/validated list. These are my budget categories. What I am looking to do is have the most common transactions auto populate the categories. i.e. if the transaction contains the word "pizza" in column C then enter "Dining Out" in column F, which is the validate list column.

From what I've seen I would have to make a formula containing all the most popular categories I want to include and put that in the cell where I want the results displayed. I can not put that formula in that column because I can only put whats in the validated list there and I also have read that IF commands can only be up to 7 IF's.

Any insight would be great, Thanks!

[jeffreybrown]

Hi walby & Welcome to the Forum,

Take a look at creating a lookup list which would negate the 7 nested IF's, however, this only applies to 2003 and lower versions of Excel.

http://www.contextures.com/xlFunctions02.html#Location

[walby]

Thanks Jeff.

It looks like what I'm looking for (I think) it may be a bit above my Excel skill set. You said its only for 2003 and older, is it worth trying to learn and apply this concept for Mac 2011?

[jeffreybrown]

The VLOOKUP will work on all Excel versions, what I was saying was about the nested IFs. Versions lower than 2007 can only handle the seven, but 2007+ does not have this limitation.

Let me know of you make it and if you get stuck, paste the formula you are trying and what you have setup as your lookup table and we can get it worked out.

[walby]

I think I may be off the mark on this vlookup. From what I see it only shows you data you already have in a sheet.

"VLOOKUP searches a list for a value in left most column and returns corresponding value from adjacent columns."

I am looking to auto populate a column based on information in another column. So when I import data in a .CSV it will automatically enter the corresponding data in a column in the same row.

Again, thank you for your advice.

[jeffreybrown]

That changes things. You did not mention a .CSV file at the beginning.

If you spell out your details (in full) maybe we can put a macro together to automate this process.

Make sure you give all the correct column references.