Excel, Compare values from different sheets, if they match return data from a third column

0
votes

I admit that I am pretty new to using formulas in Excel, so i am looking for some help. I tried looking at some other examples of similar questions and tried working through it before asking here.

Onto the question: Basically I fairly large data set. I am trying to match Names on files with Names on the employee list and then auto-fill what department they work in.

On one sheet (Lets call it Sheet1) I have HR Data the includes their name (column A) and department (Column B). On another Sheet (lets call it sheet2) I have all the file names (Column A) I want to match it to. On sheet 2 I did a vlookup to match the names on the files with the names on HR (Column B), no biggie there. But now I want to basically say IF the Name matches on Sheet2 to Sheet1, pull the value from Sheet1 Column B into Sheet2.

I tried to use an IF originally, but it seems like IF statments dont let you use a range (match Sheet2 B2 to Sheet1 A:A) and wants to match exact cells (Say Sheet2 A2 to Sheet1 A2) if that makes sense? There are people with multiple files belonging to them in sheet 2, but they are only listed once in Sheet 1.

Thank you for the help.

1
excelif-statementvlookup
Can you add some sample data into your question? There are some parts where it's tricky to understand what you want. – HaveSpacesuit
vlookup does this. It sounds like, so far, you're taking the (Sheet2, A) name and finding it in (Sheet1, A) and returning the same name... that's a good way of verifying that the data is present, but it's not the full purpose of vlookup. All you need to do is return an offset column instead (that's the 3rd argument of the formula). Simply, the formula in Sheet2, column B, could be =VLOOKUP(A1,Sheet1!A:B,2,0) where '2' means "give me the value from the second column in my defined range". – David

1 Answers

0
votes

Thank you all for responding. I know it was hard to follow what i was saying, and i really wish i could post up what im working with, however I work with PII and cant post it.

I was able to find an answer to my question with some more google searching and experimenting. If anyone has a similar problem to me, use the =INDEX funciton. The link below is what i used:

https://superuser.com/questions/669610/comparing-columns-in-two-different-excel-sheets-and-if-they-match-copying-third