Match Index To Fulfill Complete Name

[muhammad susanto]

hi all..

i need your help to fulfill complete name data with partial name given
like this table below :

nick name	complete name		entri name	target result
JARLIN...	ROMA SATRIOPUTRA		BAGAS ...	BAGAS KUSUMA
AGUS N...	HAZIZUL		HANDIK...	HANDIKA OKTAVIAN
HAZIZUL	JARLIN SUSANTO		HAZIZUL	HAZIZUL
FUJI S...	AGUS NISSON		HERIAN...	etc...
IGO DA...	FUJI SHAFAAT		IGO DA...
MAHFUD...	HANDIKA OKTAVIAN		JARLIN...
ABDUL ...	HERIANTO SITOMPUL		MAHFUD...
REFI A...	RIKI ERIK RIANTO
BAGAS ...	ARI YUGO HUTOMO
RIKI E...	REFI ADHA AMANDA
HANDIK...	BAGAS KUSUMA
ROMA S...	IGO DANANG SAPUTRA
ARI YU...	RINTO PRAMONO SETYO
RINTO ...	MAHFUD EFFENDI
HERIAN...	ABDUL ROKHIM

<tbody>
</tbody>

for assistance greatly appreciated
m.susanto

 

[AliGW]

Do the nicknames all have the ellipsis (...) after them, or is that not there in your real data?

 

[muhammad susanto]

yes, the nicknames all have the ellipsis (...) after them...

 

[AliGW]

OK. The data given is inconsistent: sometimes the ellipsis is after the final letter, sometimes it has a space before it. Is the real data also inconsistent in this way?

 

[muhammad susanto]

yes, that's real data, i export from a app....

 

[nithin shettigar]

	B	C	D	E	F
	nick name	complete name		entri name	target result
4	JARLIN...	ROMA SATRIOPUTRA		JARLIN...	JARLIN SUSANTO
5	AGUS N...	HAZIZUL		AGUS N...	AGUS NISSON
6	HAZIZUL	JARLIN SUSANTO		HAZIZUL	HAZIZUL
7	FUJI S...	AGUS NISSON		FUJI S...	FUJI SHAFAAT
8	IGO DA...	FUJI SHAFAAT		IGO DA...	IGO DANANG SAPUTRA
9	MAHFUD...	HANDIKA OKTAVIAN		MAHFUD...	MAHFUD EFFENDI
10	ABDUL ...	HERIANTO SITOMPUL		ABDUL ...	ABDUL ROKHIM
11	REFI A...	RIKI ERIK RIANTO		REFI A...	REFI ADHA AMANDA
12	BAGAS ...	ARI YUGO HUTOMO		BAGAS ...	BAGAS KUSUMA
13	RIKI E...	REFI ADHA AMANDA		RIKI E...	RIKI ERIK RIANTO
14	HANDIK...	BAGAS KUSUMA		HANDIK...	HANDIKA OKTAVIAN
15	ROMA S...	IGO DANANG SAPUTRA		ROMA S...	ROMA SATRIOPUTRA
16	ARI YU...	RINTO PRAMONO SETYO		ARI YU...	ARI YUGO HUTOMO
17	RINTO ...	MAHFUD EFFENDI		RINTO ...	RINTO PRAMONO SETYO
18	HERIAN...	ABDUL ROKHIM		HERIAN...	HERIANTO SITOMPUL

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

=OFFSET($C$3,MATCH(LEFT(E4,5)&"*",$C$3:$C$18,0)-1,0)

 

[muhammad susanto]

	B	C	D	E	F
	nick name	complete name		entri name	target result
4	JARLIN...	ROMA SATRIOPUTRA		JARLIN...	JARLIN SUSANTO
5	AGUS N...	HAZIZUL		AGUS N...	AGUS NISSON
6	HAZIZUL	JARLIN SUSANTO		HAZIZUL	HAZIZUL
7	FUJI S...	AGUS NISSON		FUJI S...	FUJI SHAFAAT
8	IGO DA...	FUJI SHAFAAT		IGO DA...	IGO DANANG SAPUTRA
9	MAHFUD...	HANDIKA OKTAVIAN		MAHFUD...	MAHFUD EFFENDI
10	ABDUL ...	HERIANTO SITOMPUL		ABDUL ...	ABDUL ROKHIM
11	REFI A...	RIKI ERIK RIANTO		REFI A...	REFI ADHA AMANDA
12	BAGAS ...	ARI YUGO HUTOMO		BAGAS ...	BAGAS KUSUMA
13	RIKI E...	REFI ADHA AMANDA		RIKI E...	RIKI ERIK RIANTO
14	HANDIK...	BAGAS KUSUMA		HANDIK...	HANDIKA OKTAVIAN
15	ROMA S...	IGO DANANG SAPUTRA		ROMA S...	ROMA SATRIOPUTRA
16	ARI YU...	RINTO PRAMONO SETYO		ARI YU...	ARI YUGO HUTOMO
17	RINTO ...	MAHFUD EFFENDI		RINTO ...	RINTO PRAMONO SETYO
18	HERIAN...	ABDUL ROKHIM		HERIAN...	HERIANTO SITOMPUL

<tbody>
</tbody>

=OFFSET($C$3,MATCH(LEFT(E4,5)&"*",$C$3:$C$18,0)-1,0)

hi nithin, you re really genius..
working 100% perfect...
many thanks...

 

[nithin shettigar]

I Think you had got your answer !!!

 

[AliGW]

I thought that you wanted an alphabetical list:

Excel 2016 (Windows) 32 bit
	A	B	C	D	E
1	nick name	complete name		entri name	target result
2	JARLIN...	ROMA SATRIOPUTRA		ABDUL ...	ABDUL ROKHIM
3	AGUS N...	HAZIZUL		AGUS N...	AGUS NISSON
4	HAZIZUL	JARLIN SUSANTO		ARI YU...	ARI YUGO HUTOMO
5	FUJI S...	AGUS NISSON		BAGAS ...	BAGAS KUSUMA
6	IGO DA...	FUJI SHAFAAT		FUJI S...	FUJI SHAFAAT
7	MAHFUD...	HANDIKA OKTAVIAN		HANDIK...	HANDIKA OKTAVIAN
8	ABDUL ...	HERIANTO SITOMPUL		HAZIZUL	HAZIZUL
9	REFI A...	RIKI ERIK RIANTO		HERIAN...	HERIANTO SITOMPUL
10	BAGAS ...	ARI YUGO HUTOMO		IGO DA...	IGO DANANG SAPUTRA
11	RIKI E...	REFI ADHA AMANDA		JARLIN...	JARLIN SUSANTO
12	HANDIK...	BAGAS KUSUMA		MAHFUD...	MAHFUD EFFENDI
13	ROMA S...	IGO DANANG SAPUTRA		REFI A...	REFI ADHA AMANDA
14	ARI YU...	RINTO PRAMONO SETYO		RIKI E...	RIKI ERIK RIANTO
15	RINTO ...	MAHFUD EFFENDI		RINTO ...	RINTO PRAMONO SETYO
16	HERIAN...	ABDUL ROKHIM		ROMA S...	ROMA SATRIOPUTRA
Sheet6

Cell Formulas
Range	Formula
E2	=IF(D2="","",VLOOKUP(LEFT(D2,5)&"*",$B$2:$B$16,1,0))
D2	{=IFERROR(INDEX($A$2:$A$16,MATCH(TRUE,IF(FREQUENCY(MATCH($A$2:$A$16,$A$2:$A$16,0),MATCH($A$2:$A$16,$A$2:$A$16,0)),COUNTIF($A$2:$A$16,"<"&$A$2:$A$16))=SMALL(IF(FREQUENCY(MATCH($A$2:$A$16,$A$2:$A$16,0),MATCH($A$2:$A$16,$A$2:$A$16,0)),COUNTIF($A$2:$A$16,"<"&$A$2:$A$16)),ROWS($1:1)),0)),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[muhammad susanto]

hi Aligw, thank you very much.....
you always support to me....