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.

 

[Michael M]

A small sample of data AND the answer you would expect from that data would help !
I'm guessing a SUMPRODUCT function might do the trick here.
Using the XL2BB download would make it easier to work with as well.....See my tage for the download link

 

[JoeMo]

Assuming your worksheet tab names are Sheet1 and Sheet2, and your data extends down to row 100 in Sheet2, in F2 of Sheet1 and copy down:

=INDEX(Sheet2!$A$2:$G$100,MATCH(Sheet1!B2&"|"&Sheet1!E2,Sheet2!$A$2:$A$100&"|"&Sheet2!$E$2:$E$100,0),7)

 

[peterman25]

A small sample of data AND the answer you would expect from that data would help !
I'm guessing a SUMPRODUCT function might do the trick here.
Using the XL2BB download would make it easier to work with as well.....See my tage for the download link

Here is an example of what I am trying to return...

I'm trying to return the value from Column G in Sheet 2 to Column F in Sheet1 based upon matching the data in Columns B and E in Sheet1 to the data in Columns A and E in Sheet2

@JoeMo - I tried your formula and received the error shown. I appreciate your efforts. Thank you.

Sheet1

Sheet2

 

[JoeMo]

@JoeMo - I tried your formula and received the error shown. I appreciate your efforts. Thank you.

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]

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.

That didn't work. Same result. I'm using Excel 2013.

 

[peterman25]

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.

Does the data have to be in exact matching order on both sheets in rows? Because it won't be. Does row 1 on each sheet need to be employee 12345?

 

[JoeMo]

Does the data have to be in exact matching order on both sheets in rows? Because it won't be. Does row 1 on each sheet need to be employee 12345?

No. Is the formula still returning an error? If not, you must not include the formula column (col F sheet1) in any sorting of the data you do.
Here's a simplified example:
Sheet1

Book1
	A	B	C	D	E	F
1	Name	ID	hdr3	hdr4	Dept	Ref Code
2	D	GHI	data	data	666	AB1000
3	C	DEF	data	data	789	AB7909
4	B	ABC	data	data	456	AB3155
5	A	XYZ	data	data	123	AB6286
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=INDEX(Sheet2!$A$2:$G$5,MATCH(Sheet1!B2&"|"&Sheet1!E2,Sheet2!$A$2:$A$5&"|"&Sheet2!$E$2:$E$5,0),7)

Sheet2

Book1
	A	B	C	D	E	F	G
1	ID	hdr2	hdr3	hdr4	Dept	hdr6	Ref Code
2	XYZ	data	data	data	123	data	AB6286
3	ABC	data	data	data	456	data	AB3155
4	DEF	data	data	data	789	data	AB7909
5	GHI	data	data	data	666	data	AB1000
Sheet2

 

[peterman25]

No. Is the formula still returning an error? If not, you must not include the formula column (col F sheet1) in any sorting of the data you do.
Here's a simplified example:
Sheet1

Book1
	A	B	C	D	E	F
1	Name	ID	hdr3	hdr4	Dept	Ref Code
2	D	GHI	data	data	666	AB1000
3	C	DEF	data	data	789	AB7909
4	B	ABC	data	data	456	AB3155
5	A	XYZ	data	data	123	AB6286
Sheet1

Cell Formulas
Range	Formula
F2:F5	F2	=INDEX(Sheet2!$A$2:$G$5,MATCH(Sheet1!B2&"|"&Sheet1!E2,Sheet2!$A$2:$A$5&"|"&Sheet2!$E$2:$E$5,0),7)

Sheet2

Book1
	A	B	C	D	E	F	G
1	ID	hdr2	hdr3	hdr4	Dept	hdr6	Ref Code
2	XYZ	data	data	data	123	data	AB6286
3	ABC	data	data	data	456	data	AB3155
4	DEF	data	data	data	789	data	AB7909
5	GHI	data	data	data	666	data	AB1000
Sheet2

I tried it myself with generic data in a brand new workbook...still errors out.

Sheet1

Sheet2

 

[peterman25]

I can get the formula to work with a single value to return data. It's when the formula is trying to combine the 2 values that it doesn't want to work.

Single value - =INDEX(Sheet2!$A$2:$G$5,MATCH(B2,Sheet2!$A$2:$A$5,0),7)

Combining the 2 values (formula is different in row 6)