2 Way Lookup

Apa itu 2 Way Lookup, yaitu mengambil data pada pertemuan kriteria Column Header (Judul Kolom) dan Row Header (Judul Baris) tertentu, untuk jelasnya, biasanya Lay Out tabelnya seperti ini:

Lay out table seperti ini lazim digunakan oleh para pengguna Excel.

PERMASALAHAN 1:
Bagaimana jika kita ingin mengetahui Nilai dari Sarah di mata pelajaran MS Word?

Tabel Data berada pada range A1:E5, dan kriteria berada di A8 dan A9:

Beberapa rumus yang bisa diaplikasikan:

1. =INDEX(B2:E5;MATCH(A8;A2:A5;0);MATCH(A9;B1:E1;0))

2. =INDEX(B2:E5;0;MATCH(A9;B1:E1;0)) INDEX(B2:E5;MATCH(A8;A2:A5;0);0)

3. =LOOKUP(2;1/(A2:A5=A8);INDEX(B2:E5;0;MATCH(A9;B1:E1;0)))

4. =VLOOKUP(A8;A2:E5;MATCH(A9;A1:E1;0);0)

5. =OFFSET(A1;MATCH(A8;A2:A5;0);MATCH(A9;B1:E1;0))

PERMASALAHAN 2:
Umpama kita sudah punya data angka "87", nah dari data ini siapa sih yang mendapatkan nilai 87 dan di mata pelajaran apa?








Untuk mengambil Column Header, rumusnya:
=INDEX(B1:E1;;MAX(IF(B2:E5=D8;COLUMN(B21:E21)-COLUMN(B21)+1)))

Untuk mengambil Row Header, rumusnya:
=INDEX(A2:A5;MAX(IF(B2:E5=D8;ROW(A2:A5)-ROW(A2)+1)))

2 terakhir ini Array Formula ya.... dan untuk catatan kondisi value/data angka yang ada di tabel tidak terdapat data yang sama/dobel. Untuk data dobel dengan lay out tabel seperti ini rumusnya akan berbeda lagi.

Unduh FIle Latihan

Salam.....

Mempersingkat Nama Depan, Tengah dan Nama Belakang

Ass.Wr.Wb.

Selamat siang para pembaca, hari ini saya akan posting rumus untuk memodifikasi teks, kebetulan ada kasus mempersingkat nama. Nah biasanya yang lazim yang disingkat adalah nama depan, nama tengah, tapi nama belakang disingkat juga lazim sih....

Joko Santoso Mulyono disingkat menjadiJ. Santoso Mulyono atau Joko S. Mulyono atau Joko Santoso M.

Solusi Mempersingkat Nama Depan:

=LEFT(A1;1)&". "&MID(A1;FIND(" ";A1)+1;255)

atau

=REPLACE(A1;2;FIND(" ";A1)-2;".")

Solusi Mempersingkat Nama Tengah:

=REPLACE(A1;FIND(" ";A1)+2;FIND(" ";A1;FIND(" ";A1)+1)-FIND(" ";A1)-2;".")

atau

=CHOOSE(LEN(A1)-LEN(SUBSTITUTE(A1;" ";""));A1;SUBSTITUTE(A1;MID(A1;SEARCH(" ";A1)+1;SEARCH(" ";A1;SEARCH(" ";A1)+1)-SEARCH(" ";A1));MID(A1;SEARCH(" ";A1)+1;1)&". "))


Solusi Mempersingkat Nama Belakang:

=TRIM(LEFT(A1;SEARCH(" ";A1;SEARCH(" ";A1;1)+1)))&" "&MID(A1;FIND(" ";A1;FIND(" ";A1;1)+1)+1;1)&"."

atau

=LEFT(TRIM(A1);FIND("~";SUBSTITUTE(A1;" ";"~";LEN(TRIM(A1))-LEN(SUBSTITUTE(TRIM(A1);" ";""))))-1)&" "&MID(A1;FIND("~";SUBSTITUTE(A1;" ";"~";LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))))+1;1)&"."

 Solusi Mempersingkat 2 Nama Depan:

=LEFT(A1;1)&"."&(MID(A1;FIND(" ";A1;1)+1;1))&". "&TRIM(RIGHT(SUBSTITUTE(A1;" ";REPT(" ";256));256))


Solusi Membuat Inisial:

Misalkan dari contoh nama diatas kita ingin membuat inisialnya, dari Joko Santosa Mulyono menjadi JSM, nah rumusnya.....

=IFERROR(LEFT(A1;1)&(MID(A1;FIND(" ";A1;1)+1;1))&MID(A1;FIND(" ";A1;FIND(" ";A1)+1)+1;1);LEFT(A1;1)&(MID(A1;FIND(" ";A1;1)+1;1)))

atau

=IF(LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))=0;LEFT(A1;1);IF(LEN(A1)-LEN(SUBSTITUTE(A1;" ";""))=1;LEFT(A1;1)&MID(A1;FIND(" ";A1)+1;1);LEFT(A1;1)&MID(A1;FIND(" ";A1)+1;1)&MID(A1;FIND(" ";A1;FIND(" ";A1)+1)+1;1)))

Dicoba ya.....matursuwun

Rizky

Download File Latihan