Need help formula to find exact value

[Budi_K]

Hi,

Can someone help me to solve my problem.
To find exact value for Column B2 or C2 refer to Column A2.
Array Table at Sheet 2.

Sheet 1

Exercise.xlsx
	A	B	C	D	E
1	Data ID	Old ID (Index Match)	New ID (Index Match)	Old ID (Vlookup)	New ID (Vlookup)
2	16GSK0083	0	0	#N/A	#N/A
3	20SBY782	0	0	16SBY0268	16SBY0268
4	16GSK0083	0	0	#N/A	#N/A
5	130041	0	0	#N/A	#N/A
6	16GSK0158	0	0	#N/A	#N/A
7	16GSK0155	0	0	#N/A	#N/A
8	630497	0	0	16SDA0082	16SDA0082
9	16GSK0113	0	0	#N/A	#N/A
Sheet1

Cell Formulas
Range	Formula
B2:B9	B2	=INDEX(Sheet2!A:B,MATCH(A2,A2:A9&B2:B9,0))
C2:C9	C2	=INDEX(Sheet2!A:B,MATCH(A2,A2:A9&B2:B9,0))
D2	D2	=IFERROR(VLOOKUP(A2,Sheet2!A:B,2),VLOOKUP(A2,Sheet2!A:B,1))
E2:E9	E2	=IFERROR(VLOOKUP(A2,Sheet2!A:B,2),VLOOKUP(A2,Sheet2!A:B,1))
D3:D9	D3	=IFERROR(VLOOKUP(A3,Sheet2!A:B,2),VLOOKUP(A3,Sheet2!A:B,2))
Press CTRL+SHIFT+ENTER to enter array formulas.

Sheet 2

Old ID	New ID
630294	16GSK0113
132914	16GSK0155
630497	16GSK0195
130049	16GSK0158
130041	16GSK0204
630237	16GSK0176
20SBY782	16SBY0268
130048	16GSK0083
132919	16SDA0082

Thank You

 

[bebo021999]

Book1
	A	B	C
1	Data ID	Old ID	New ID
2	16GSK0083	130048	16GSK0083
3	20SBY782	20SBY782	16SBY0268
4	16GSK0083	130048	16GSK0083
5	130041	130041	16GSK0204
6	16GSK0158	130049	16GSK0158
7	16GSK0155	132914	16GSK0155
8	630497	630497	16GSK0195
9	16GSK0113	630294	16GSK0113
Sheet1

Cell Formulas
Range	Formula
B2:C9	B2	=INDEX((Sheet2!$A$2:$A$10,Sheet2!$B$2:$B$10),IFERROR(MATCH($A2,Sheet2!$A$2:$A$10,0),MATCH($A2,Sheet2!$B$2:$B$10,0)),1,COLUMNS($A:A))

Book1
	A	B
1	Old ID	New ID
2	630294	16GSK0113
3	132914	16GSK0155
4	630497	16GSK0195
5	130049	16GSK0158
6	130041	16GSK0204
7	630237	16GSK0176
8	20SBY782	16SBY0268
9	130048	16GSK0083
10	132919	16SDA0082
Sheet2

 

[Budi_K]

Book1
	A	B	C
1	Data ID	Old ID	New ID
2	16GSK0083	130048	16GSK0083
3	20SBY782	20SBY782	16SBY0268
4	16GSK0083	130048	16GSK0083
5	130041	130041	16GSK0204
6	16GSK0158	130049	16GSK0158
7	16GSK0155	132914	16GSK0155
8	630497	630497	16GSK0195
9	16GSK0113	630294	16GSK0113
Sheet1

Cell Formulas
Range	Formula
B2:C9	B2	=INDEX((Sheet2!$A$2:$A$10,Sheet2!$B$2:$B$10),IFERROR(MATCH($A2,Sheet2!$A$2:$A$10,0),MATCH($A2,Sheet2!$B$2:$B$10,0)),1,COLUMNS($A:A))

Book1
	A	B
1	Old ID	New ID
2	630294	16GSK0113
3	132914	16GSK0155
4	630497	16GSK0195
5	130049	16GSK0158
6	130041	16GSK0204
7	630237	16GSK0176
8	20SBY782	16SBY0268
9	130048	16GSK0083
10	132919	16SDA0082
Sheet2

Thank you very much, Sir.
Thank you