# Change cell bases on contents of another cell

#### JonnyOxford

##### New Member
Good Morning,

I have a list of approximately 100 names. Every morning they clock in to a finger print scanner. I receive this as a simple excel spreadsheet which shows their name and clock in time.

I need to be able to add a extra cell next to their name with their job title so that I can then run COUNTIF to see how many men from each job title are working. I have an excel spreadsheet with their names and job roles so I just need to be able to link them somehow?

Because the names always come in in a different order and often there are people who don't turn up the sequence changes everyday, otherwise I could have sorted the data and simply copy and pasted the job titles from the day before.

The reason for this is that I need to produce a graph at the end of each week showing how many of each job title have worked during the week.

I hope this makes sense.

Thanks

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
Can you post some sample data?

Try vlookup,

=vlookup(name to lookup, sheet & columns with name & job title,2,0)

e.g.
=VLOOKUP(A1,Sheet2!\$A\$1:\$B\$30,2,0)

Last edited:
Can you post some sample data?

I've uploaded the file to WeTransfer. The first sheet is the data I received today, the second sheet is the information I have made. I have removed their names and replaced with name 1, name 2..... I want to make a new column on the first sheet which will look at sheet 2 and see that Name 1 is a Concrete Finisher so populate the cell with this title.

I have two jobs with very similar data, this is the smaller of the two.

https://we.tl/t-JrN4aQSUF1

Did you try vlookup?

Hi Johnny,

Just like as gaz chops suggested vlookup will populate Column E with roles from sheet 1 for your countif;

=VLOOKUP(C4,'Job Titles'!\$D\$1:\$E\$46,2,0)

Awesome that works. Please excuse by excel knowledge.

Thanks for your help gaz_chops and RasGhul!!

Replies
2
Views
224
Replies
0
Views
187
Replies
8
Views
343
Replies
3
Views
195
Replies
1
Views
281

1,206,970
Messages
6,075,921
Members
446,170
Latest member
zzzz02

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back