Find the match within the sheet and return next cell value

[Chand1987]

Hope someone can help me in this forum,
I have a worksheet like below and if I make a list KS-C08, KS-B08, KS-C07..... I want to get return of ID and Name in Next column.

Result Expected

KS-C08​	T_SK-006​	Jae Sun Lee​
KS-B08​	T_SK-003​	Yongkwang Kim​
KS-C07​		Dongwan lim​
KS-B07​		Hyunbo Sim​
KS-C06​	T_SK-015​	Jinhwan Kim​
KS-B06​	T_SK-011​	Dojin Kim​

1. Work Sheet

KS-C08		KS-B08
T_SK-006	Jae Sun Lee​	T_SK-003	Yongkwang Kim
KS-C07		KS-B07
	Dongwan lim		Hyunbo Sim
KS-C06		KS-B06
T_SK-015	Jinhwan Kim	T_SK-011	Dojin Kim
KS-C05		KS-B05
T_SK-013	Nameun Kim	T_SK-014	Youngjun Kim
KS-C04		KS-B04
T_SK-009	Young Rok Hong​	T_SK-007	Byungwon Yoo​
KS-C03	~6/16	KS-B03	6/1~
BT	Kwanhyung Nam		Ilkeun Youn​
KS-C02		KS-B02

 

[hrayani]

Hi,
I am not sure if there is a better way of doing it or not
but you can try like this

Book6
	A	B	C	D	E	F	G	H
1	1. Work Sheet						ID	NAME
2	KS-C08		KS-B08			KS-C08	T_SK-006	Jae Sun Lee
3	T_SK-006	Jae Sun Lee	T_SK-003	Yongkwang Kim		KS-B08	T_SK-003	Yongkwang Kim
4						KS-C07		Dongwan lim
5	KS-C07		KS-B07			KS-B07		Hyunbo Sim
6		Dongwan lim		Hyunbo Sim		KS-C06	T_SK-015	Jinhwan Kim
7						KS-B06	T_SK-011	Dojin Kim
8	KS-C06		KS-B06
9	T_SK-015	Jinhwan Kim	T_SK-011	Dojin Kim
10
11	KS-C05		KS-B05
12	T_SK-013	Nameun Kim	T_SK-014	Youngjun Kim
13
14	KS-C04		KS-B04
15	T_SK-009	Young Rok Hong	T_SK-007	Byungwon Yoo
16
17	KS-C03	~6/16	KS-B03	6/1~
18	BT	Kwanhyung Nam		Ilkeun Youn
19
20	KS-C02		KS-B02
Sheet2

Cell Formulas
Range	Formula
G9,G2:G7	G2	=IFERROR(INDEX(OFFSET($A$2:$A$20,1,0),MATCH(F2,$A$2:$A$20,0)),"")&IFERROR(INDEX(OFFSET($C$2:$C$20,1,0),MATCH(F2,$C$2:$C$20,0)),"")
H9,H2:H7	H2	=IFERROR(INDEX(OFFSET($A$2:$A$20,1,1),MATCH(F2,$A$2:$A$20,0)),"")&IFERROR(INDEX(OFFSET($C$2:$C$20,1,1),MATCH(F2,$C$2:$C$20,0)),"")

 

[Chand1987]

Hi,
I am not sure if there is a better way of doing it or not
but you can try like this

Book6
	A	B	C	D	E	F	G	H
1	1. Work Sheet						ID	NAME
2	KS-C08		KS-B08			KS-C08	T_SK-006	Jae Sun Lee
3	T_SK-006	Jae Sun Lee	T_SK-003	Yongkwang Kim		KS-B08	T_SK-003	Yongkwang Kim
4						KS-C07		Dongwan lim
5	KS-C07		KS-B07			KS-B07		Hyunbo Sim
6		Dongwan lim		Hyunbo Sim		KS-C06	T_SK-015	Jinhwan Kim
7						KS-B06	T_SK-011	Dojin Kim
8	KS-C06		KS-B06
9	T_SK-015	Jinhwan Kim	T_SK-011	Dojin Kim
10
11	KS-C05		KS-B05
12	T_SK-013	Nameun Kim	T_SK-014	Youngjun Kim
13
14	KS-C04		KS-B04
15	T_SK-009	Young Rok Hong	T_SK-007	Byungwon Yoo
16
17	KS-C03	~6/16	KS-B03	6/1~
18	BT	Kwanhyung Nam		Ilkeun Youn
19
20	KS-C02		KS-B02
Sheet2

Cell Formulas
Range	Formula
G9,G2:G7	G2	=IFERROR(INDEX(OFFSET($A$2:$A$20,1,0),MATCH(F2,$A$2:$A$20,0)),"")&IFERROR(INDEX(OFFSET($C$2:$C$20,1,0),MATCH(F2,$C$2:$C$20,0)),"")
H9,H2:H7	H2	=IFERROR(INDEX(OFFSET($A$2:$A$20,1,1),MATCH(F2,$A$2:$A$20,0)),"")&IFERROR(INDEX(OFFSET($C$2:$C$20,1,1),MATCH(F2,$C$2:$C$20,0)),"")

Thank you for your quick response, But i am expecting formula that with the range like A5:Z:500 instead of Selecting A, C, or E ....