2 cells in on row match 2 cells in another row. Take other data and copy

[TylerH]

Hello,

i have tried to do this but can’t seem to figure it out.

i have a work book with 2 sheets and I want it to match two columns with two columns on anouthher sheet and if they are the same then to copy anouthrr column. Example bellow

sheet1
A................. B. .... E
Cable#.......Side......unit
1. Cable1 _____. Unit1.
2. Cable 1 _____. Unit2.
3. Cable 2. ______. Unit1.
4. Cable2. ______. Unit3.

sheet 2
A. ............B. ..........C
Cable#. Unit#..... Side
1. Cable1 Unit1. SideA
2. Cable 1 Unit2. SideB
3. Cable 2. Unit1. SideA
4. Cable2. Unit3. SideB

So what I need is if sheet1 A1&E1 are the same as sheet2 A1&B1 then sheet2 C1 will copy to sheet1 B1


note the lines on sheet 2 are all over the place but the unit will always go with the cabl# on both sheets. There are multiple lines with the same unit which is why it has to match the cable#. The side a & b of the cable # will always be different unit #s

Also sheet 2 is in a table if that helps


thanks in advance

tyler

 

[Scott T]

try

Excel 2010
	A	B	C	D	E
1	Cable1	SideA			Unit1
2	Cable1	SideB			unit2
3	Cable2	SideA			Unit1
4	Cable2	SideB			unit3
Sheet1

Cell Formulas
Range	Formula
B1	{=IFERROR(INDEX(Sheet2!$C$1:$C$4,MATCH(A1&E1,Sheet2!$A$1:$A$4&Sheet2!$B$1:$B$4,0)),"no match")}
B2	{=IFERROR(INDEX(Sheet2!$C$1:$C$4,MATCH(A2&E2,Sheet2!$A$1:$A$4&Sheet2!$B$1:$B$4,0)),"no match")}
B3	{=IFERROR(INDEX(Sheet2!$C$1:$C$4,MATCH(A3&E3,Sheet2!$A$1:$A$4&Sheet2!$B$1:$B$4,0)),"no match")}
B4	{=IFERROR(INDEX(Sheet2!$C$1:$C$4,MATCH(A4&E4,Sheet2!$A$1:$A$4&Sheet2!$B$1:$B$4,0)),"no match")}
Press CTRL+SHIFT+ENTER to enter array formulas.

Excel 2010
	A	B	C
1	Cable1	Unit1	SideA
2	Cable1	unit2	SideB
3	Cable2	Unit1	SideA
4	Cable2	unit3	SideB
Sheet2

 

[Fluff]

Cross posted https://www.ozgrid.com/forum/forum/...-row-match-2-cells-in-another-row-copy-a-cell

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[Fluff]

Resolved on Ozgrid