Comparing 2 Cells again 2 different Cells and return an offset

[MartinSmith]

Hi quite new to this,

My question is how to compare 2 cells in sheet 1 against 2 cells in sheet 2 and if the cells in sheet 1 match those in sheet 2 then return an offset of from the match cells in sheet 2.

Index, Match or vLookUp??? Is there another database function?

Looking forward to your response

 

[Fishboy]

Hi quite new to this,

My question is how to compare 2 cells in sheet 1 against 2 cells in sheet 2 and if the cells in sheet 1 match those in sheet 2 then return an offset of from the match cells in sheet 2.

Index, Match or vLookUp??? Is there another database function?

Looking forward to your response

Hi Martin, welcome to the boards.

You will need to give us some more specific details such as what cells are being compared and exactly what the offset you need. Once we have those details you stand a better chance of getting assistance.

Based on what little information we have been given I can at least outline how I envision the formula working:

It would start with something like =(IF(AND(Sheet1!A1=Sheet2!A1,Sheet1!B1=Sheet2!B1) and then end with an index match.

As you can see without the specifics there isn't much we can do for you.

 

[MartinSmith]

Hi Fishboy

This is Sheet1 and the it is Columns D (Zone) and E (Station) that need to be completed depending on the values in A (Team) & B (Part code). Looking up on Team and Partcode in sheet2 and returning the Zone and station information from Sheet2 columns E&F

I hope this is a little bit clearer now! Let me know if you need more info.

Thanks Martin

Sheet1
[TABLE="width: 392"]
<tbody>[TR]
[TD] [/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team[/TD]
[TD]Part code[/TD]
[TD] [/TD]
[TD]Zone[/TD]
[TD]Station[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4K2[/TD]
[TD]BIW[/TD]
[TD] [/TD]
[TD] Formula[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4K2[/TD]
[TD]BIW[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4K6[/TD]
[TD]BM[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4E1[/TD]
[TD]CAB[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4K3[/TD]
[TD]FB[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4K7[/TD]
[TD]DG[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4K7[/TD]
[TD]DSB[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4K3[/TD]
[TD]UTH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]4K3[/TD]
[TD]UTH[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]4K7[/TD]
[TD]GLZ[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4K7[/TD]
[TD]DG[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]4E2[/TD]
[TD]SWS[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]4A1[/TD]
[TD]I1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]4E5[/TD]
[TD]I1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]4E5[/TD]
[TD]I1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col span="2"></colgroup>[/TABLE]

Sheet2
[TABLE="width: 697"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team Code[/TD]
[TD]Product Description[/TD]
[TD]Part code[/TD]
[TD][/TD]
[TD]ZONE[/TD]
[TD]STATION[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]4A1[/TD]
[TD]Widget 1[/TD]
[TD]CE[/TD]
[TD][/TD]
[TD]Zone 1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4K2[/TD]
[TD]Widget 2[/TD]
[TD]BIW[/TD]
[TD][/TD]
[TD]Zone 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]l1[/TD]
[TD]Widget 3[/TD]
[TD]l1[/TD]
[TD][/TD]
[TD]Zone 2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]4A1[/TD]
[TD]Widget 4[/TD]
[TD]EIW[/TD]
[TD][/TD]
[TD]Zone 3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4A1[/TD]
[TD]Widget 5[/TD]
[TD]ESU[/TD]
[TD][/TD]
[TD]Zone 2[/TD]
[TD]5[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>[/TABLE]

 

[Peter_SSs]

For Sheet1 try this, copied across to E2 and down - after adjusting the row range for Sheet2 to cover at least as many rows as you will likely ever have.

D2: =INDEX(Sheet2!E$2:E$1000,MATCH($A2&"|"&$B2,INDEX(Sheet2!$A$2:$A$1000&"|"&Sheet2!$C$2:$C$1000,0),0))

 

[MartinSmith]

Hi Peter_SSs

That's the solution, works like a charm.

I thought I knew Excel until I came across this site!

Thank you both for your fast response.

Martin

 

[Fishboy]

Thank you both for your fast response.

You're welcome

 

[Peter_SSs]

Hi Peter_SSs

That's the solution, works like a charm.

I thought I knew Excel until I came across this site!

Thank you both for your fast response.

Martin

Glad it worked for you. Thanks for letting us know.