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

	A	B	C	D	E
1	Team	Part code		Zone	Station
2	4K2	BIW		Formula	Formula
3	4K2	BIW
4	4K6	BM
5	4E1	CAB
6	4K3	FB
7	4K7	DG
8	4K7	DSB
9	4K3	UTH
10	4K3	UTH
11	4K7	GLZ
12	4K7	DG
13	4E2	SWS
14	4A1	I1
15	4E5	I1
16	4E5	I1

<tbody>
</tbody><colgroup><col><col><col><col><col span="2"></colgroup>

Sheet2

	A	B	C	D	E	F
1	Team Code	Product Description	Part code		ZONE	STATION
2	4A1	Widget 1	CE		Zone 1	1
3	4K2	Widget 2	BIW		Zone 1	2
4	l1	Widget 3	l1		Zone 2	1
5	4A1	Widget 4	EIW		Zone 3	3
6	4A1	Widget 5	ESU		Zone 2	5

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

 

[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.