LATIHAN SOAL EXCEL: Juni 2021

Ternyata, Vlookup ini juga memiliki kelemahan, yaitu tidak bisa mencari data yang posisinya terletak di sebelah kiri lookup value (nilai kunci). Oleh karena itu Vlookup harus “dibantu” oleh rumus lain, dengan cara digabung. Atau bisa juga mengganti Vlookup dengan menggunakan rumus-rumus lainya.

Perhatikan gambar dibawah ini!

Pada Tabel 1, Kode Kamar terletak di kolom ke 5. Maka kita tidak bisa mencari nilai Nama, Fakultas dan Asal (yang ada di Tabel 2) dengan menggunakan rumus Vlookup. Kenapa? karena kolom: Nama, Fakultas, Angkatan dan Asal (yang ada di tabel 1) terletak disebelah kiri kolom Kode Kamar.

Untuk menyiasatinya, ada beberapa rumus gabungan yang bisa dipakai untuk menutupi kelemahan Vlookup ini, diantaranya (sepengetahuan penulis tentunya):

I. Gabungan OFFSET dan MATCH

II. Gabungan INDEX dan MATCH

III. Gabungan HLOOKUP dan MATCH

IV. Gabungan VLOOKUP dan IF

V. Gabungan VLOOKUP dan CHOOSE

Sebenarnya masih ada beberapa rumus gabungan lagi yang bisa digunakan. Sayangnya, sampai artikel ini saya tulis, hanya 5 rumus gabungan ini yang masih saya kuasai.

I. Gabungan OFFSET dan MATCH

Ini merupakan penggabungan antara rumus: OFFSET dan MATCH.

= OFFSET (Reference; Rows; Cols; [Height]; [Width])

=MATCH (Lookup_value; Lookup_array; [Match_type])

Jadi Rows (pada Rumus OFFSET) disubstitusi atau diganti dengan Rumus MATCH.

Rumus gabungannya menjadi:

= OFFSET (Reference; MATCH (Lookup_value; Lookup_array; [Match_type]); Cols; [Height]; [Width])

Dibawah ini saya berikan contoh soal sederhana:

Lihat gambar anak panah diatas, untuk mencari Nama digunakan rumus:

=OFFSET( G5; MATCH( O7; K6:K14; 0); 0)

Keterangan:

· Sel G5 merupakan Judul kolom yang dicari nilainya

· Sel O7 merupakan nilai kunci dari fungsi Match

· K6:K14 area yang di blok berdasarkan nilai kunci

· 0 merupakan Match_type dari fungsi Match

· 0 merupakan Kolom dari fungsi Offse

Video Tutorial dari penjelasan diatas bisa anda lihat melalui kanal Youtube penulis. Klik video dibawah ini:

https://youtu.be/GKM1gmCugL4

II. Gabungan INDEX dan MATCH

Ini merupakan penggabungan antara rumus INDEX dan rumus MATCH.

=INDEX (Array; Rows_num; [Cols_num])

=MATCH (Lookup_value; Lookup_array; [Match_type])

Jadi Rows_num (pada Rumus INDEX) disubstitusi atau diganti dengan Rumus MATCH.

Rumus gabungannya menjadi:

=INDEX (Array; Rows_num; MATCH (Lookup_value; Lookup_array; [Match_type]); [Cols_num])

Dibawah ini saya berikan contoh soal sederhana:

Lihat gambar anak panah diatas, untuk mencari nama digunakan rumus:

=INDEX( S6:S14; MATCH( AA7; W6:W14; 0))

Keterangan:

· S6:S14 merupakan area yang dicari nilainya

· AA7 merupakan nilai kunci dari fungsi Match

· W6:W14 area yang diblok berdasarkan nilai kunci

· 0 merupakan match_type dari fungsi Match

Video Tutorial dari penjelasan diatas bisa anda lihat melalui kanal Youtube penulis. Klik video dibawah ini:

https://www.youtube.com/watch?v=ySnbP9vCvaM

III. Gabungan HLOOKUP dan MATCH

Ini merupakan penggabungan antara rumus: HLOOKUP dan MATCH.

=HLOOKUP (Lookup_value; Table_array; Row_index_num; [Range_lookup])

=MATCH (Lookup_value; Lookup_array; [Match_type])

Jadi Rows_index_num (pada Rumus HLOOKUP) disubstitusi atau diganti dengan Rumus MATCH.

Rumus gabungannya menjadi:

=HLOOKUP (Lookup_value; Table_array; MATCH (Lookup_value; Lookup_array; [Match_type]); [Range_lookup])

Dibawah ini saya berikan contoh soal sederhana:

Lihat gambar anak panah diatas, untuk mencari nama digunakan rumus:

=HLOOKUP( N28; G25:L34; MATCH( O27; K25:K34; 0); FALSE)

Keterangan:

· N28 merupakan nilai kunci dari fungsi Hlookup

· G25:L34 seluruh tabel diblok (dimulai dari judul kolomnya)

· O27 merupakan nilai kunci dari fungsi Match

· K25:K34 area yang diblok berdasarkan nilai kunci

· 0 merupakan match_type dari fungsi Match

· False agar hasilnya bernilai mutlak/ absolut

Video Tutorial dari penjelasan diatas bisa anda lihat melalui kanal Youtube penulis. Klik video dibawah ini:

https://www.youtube.com/watch?v=IydhvKTBatI

IV. Gabungan VLOOKUP dan IF

Ini merupakan penggabungan antara rumus: VLOOKUP dan IF.

=VLOOKUP (Lookup_value; Table_array; Col_index_num; [Range_lookup])

=IF (Logical_test; [Value_if_true]; [Value_if_false])

Jadi Table_array (pada Rumus VLOOKUP) disubstitusi atau diganti dengan Rumus IF.

Rumus gabungannya menjadi:

=VLOOKUP (Lookup_value; IF (Logical_test; [Value_if_true]; [Value_if_false]); Col_index_num; [Range_lookup])

Dibawah ini saya berikan contoh soal sederhana:

Lihat gambar anak panah diatas, untuk mencari nama digunakan rumus:

=VLOOKUP( AA27; IF({1\0}; W26:W34; S26:S34); 2; FALSE)

Keterangan:

· AA27 merupakan nilai kunci dari fungsi Vlookup

· 1\0 merupakan logika test dari fungsi If

· W26:W34 adalah nilai logika yang dianggap benar

· S26:S34 adalah nilai logika yang dianggap salah

· 2 merupakan nomor kolom yang dicari

· False agar hasilnya bernilai mutlak/ absolut

Video Tutorial dari penjelasan diatas bisa anda lihat melalui kanal Youtube penulis. Klik video dibawah ini:

https://youtu.be/jT6CpF8gNIY

V. Gabungan VLOOKUP dan CHOOSE

Ini merupakan penggabungan antara rumus: VLOOKUP dan CHOOSE.

=VLOOKUP (Lookup_value; Table_array; Col_index_num; [Range_lookup])

=CHOOSE (Indeks_num; Value 1; [Value 2]; ...)

Jadi Table_array (pada Rumus VLOOKUP) disubstitusi atau diganti dengan Rumus CHOOSE.

Rumus gabungannya menjadi:

=VLOOKUP (Lookup_value; CHOOSE (Indeks_num; Value 1; [Value 2]; ...); Col_index_num; [Range_lookup])

Dibawah ini saya berikan contoh soal sederhana:

Lihat gambar anak panah diatas, untuk mencari nama digunakan rumus:

=VLOOKUP( AN27; CHOOSE({1\5}; AJ26:AJ34; AI26:AI34; AH26:AH34; AG26:AG34; AF26:AF34); 2; FALSE)

Disini penulis tidak akan menjelaskan rumus diatas karena terlalu panjang dan saya pikir tidak efektif apabila dijelaskan satu persatu.

Video Tutorial dari penjelasan diatas bisa anda lihat melalui kanal Youtube penulis. Klik video dibawah ini:

https://youtu.be/xWgUqADpK_M

RUMUS XLOOKUP

Untuk mengatasi kelemahan fungsi Vlookup ini sebenarnya ada rumus baru yaitu XLOOKUP. Sayangnya fungsi ini hanya ada di MS. Office versi 365. Kebetulan MS. Office saya masih versi yang lama, jadinya tidak bisa mengulas penggunaan rumus ini.

Setidak-tidaknya 5 rumus gabungan diatas bisa dijadikan alternatif untuk mengatasi kekurangan Fungsi Vlookup. Saran saya, dari 5 rumus gabungan diatas maka rumus I dan II yang paling praktis dan mudah untuk digunakan

Sekian dari saya semoga bermanfaat bye.... ^{*By Muji Yamianto}