Excel 2007 : Formula to link data from one cell to another?

[driveherdown]

I have a group of Department Names in Row B that have a Corresponding Department number in Row A. So in Row A2 it shows "1" and in Row B2 it shows "Marketing". So Marketing is Department "1".

Im trying to figure out a formula that will allow me to type the Department number in a cell and have it automatically change to the Department Name.

So for example, If I type "1" in Cell C2 it needs to change to "Marketing".

[aff219]

You mentioned A, B, and C2, but I think you meant to only mention A and B (this example is with A being the # and B being the department name)

Plug this into B2:
=IF(A2=1,"Marketing",FORMULA)

example with multiple possibilities (Marketing, Sales, Customer Service):
=IF(A2=1,"Marketing",IF(A2=2,"Sales",IF(A2=3,"Customer Service")))

after you create the formula for one cell, you can copy/paste it into other cells in the same column and it'll automatically adjust the formula to match the proper areas

[driveherdown]

Thanks! that helped a lot, I was able to tweak the formula you gave me, but now im running into another issue.

This is basically what my formula looks like ( I have "$" in place but removed them for the sake of visibility)

=IF(A2:A1000=B2,C2,IF(A2:A1000=B3,C3,IF(A2:A1000=B4,C4,IF(A2:A1000=B5,C5))))


The problem is this.....

Cells B2 through B1000 have the Department Number
Cells C2 through C1000 have the Department Name

The formula effects cells Cells A2 through A1000.

Right now im adjusting the formula manually, but that means i have to put in 1000 entries into the formula. Is there anyway to auto-complete the formula so it covers all 1000 Department names and numbers?