Look at 2 values to return one result

[peterman25]

I've been racking my brain on this one, but can't seem to get it...

I am looking at 2 pieces of data, but want to return one result. The result comes from a different worksheet (table). I'm wanting to do so without moving data around. I know that I could concatenate the 2 pieces of data in the 2 worksheets and then just do a vlookup, but is it possible without having to do those steps and just leaving the worksheets as they are dumped out of the systems that create them?

Worksheet 1 - Column B is an employee ID that is unique. Column E is a department code. What I am trying to do is look at both values to return a job reference code to column F.

Worksheet 2 - Has the employee ID (column A) and the department code (column E) AND the job reference code (column G) that I am trying to return.

Thank you for any assistance / guidance.

 

[Fluff]

You missed what Joe said in post#5

I'm using Excel for Office 365. If you are not, try confirming the formula with ctrl+shift+Enter to make it an array formula and then copy it down.

 

[peterman25]

You missed what Joe said in post#5

Thanks. I figured it out and it works now. You have to do the ctrl+shift+enter in the formula bar for it to work properly.

 

[JoeMo]

Thanks. I figured it out and it works now. You have to do the ctrl+shift+enter in the formula bar for it to work properly.

You are welcome - thanks for the reply, and thanks to Fluff for redirecting you to post #5.