Pertanyaan:
"Tempat belajar excel paling OK ya XL-maniaaa... Gitu kata orang-orang! Langsung tanya nih boss, gimana ya caranya cari data berdasarkan banyak kriteria atau lookup berdasarkan banyak kriteria? Umumnya sih saya sering cari data berdasarkan dua kriteria, tapi sekarang saya perlu rumus untuk cari data berdasar 3 kriteria. Lagi pusing-pusing mikir, temen saya tanya, gimana caranya lookup berdasarkan 4 kriteria... Pusing... Pusing... Pusing... Contoh data terlampir." - Obama Mc Cain, Amerika.
Jawab:Pertanyaan: "lookup berdasar banyak kriteria" dan "mencari data berdasar banyak kriteria" adalah salah satu topik yang cukup populer di XL-mania. Pertanyaan ini sering muncul lebih dari 3kali per minggu sehingga terkadang perlu difilter. Anyway, mulai sekarang member XL-mania dapat menemukan artikelnya di sini :) hehehe... Untuk contoh data seperti terlampir, formula yang dapat digunakan di F14 adalah:
{=INDEX($F$3:$F$11,MATCH(B14&C14&D14&E14,$B$3:$B$11&$C$3:$C$11&$D$3:$D$11&$E$3:$E$11))}
kemudian dicopy ke range F15:F16. Formula ini adalah formula array sehingga untuk mengakhirinya harus menekan Ctrl+Shift+Enter.
Penjelasan:
Dasar formula ini sama dengan formula "vlookup yang bisa nengok ke kiri"
=INDEX($F$3:$F$11,...) berfungsi untuk memberikan nilai pada baris yang ditunjuk.
Sedangkan ...MATCH(...,...))... berfungsi untuk mencari pada baris berapa data yang sesuai
Pada fungsi MATCH, data yang dicari adalah gabungan dari kolom B, C, D, dan E, sehingga data yang dicari dituliskan sebagai ...B14&C14&D14&E14...
Formula ini bukanlah satu-satunya cara, masih banyak cara lain di sini
File:
Anda dapat mendownload materi tips ini di sini
[harus memiliki yahoo id dan mengikuti mailing list XL-mania, tempat belajar microsoft excel!]
Sub KomentarSaya()
ReplyDeleteGABUNG_XL_MANIA = True
Do
If Not GABUNG_XL_MANIA Then
kerja_manual = True
If MsgBox("Masih Ingin Kerja Manual ? ", vbYesNo) = vbYes Then
GABUNG_XL_MANIA = False
Else
GABUNG_XL_MANIA = True
End If
End If
Loop Until GABUNG_XL_MANIA
End Sub
Yang gini-gini nih yang manteb n kreatif. Ilmunya bisa dipake orang banyak. Seneng aku sama anak-anak indo yang pinter-pinter gini.
ReplyDeleteHehehehe....
Terima kasih untuk blog yang menarik
ReplyDeleteMas, bagaimana mencari dengan sebagian kriteria. Misal (menggunakan tabel pada contoh di atas), pada kolom pencarian, kita menulis:
ReplyDeletekriteria 1 = aaa
kriteria 2 = xxx
kriteria 3 = ape
kriteria 4 = ber
hasil yang kita inginkan = xl-mania
Terimakasih
saya lagi ada masalah tlg dibantu donk...boss ABIE atu expertMania yg laen...bagaimana sumif dgn bbrp kriteria yg salah satu kriterianya berdasarkan tanggal???ini berhub dgn stock brg..saya gk bisa cara memasukkan fungsi tanggal dalm formula excel...misalkan hasil yg dicari berdasarkan kriteria aaa,bbb,dan tgl keluar brg.mksh semogamilmu yg dibagikan bermanfaat n membawa berkah bwt boss abie n semua komunitas xl mania ...
ReplyDeleteDengan menggunakan rumus andalan sumproduct
ReplyDeleteini blog yang ak cari selama ini...
ReplyDeletemhn ilmunya macro excel ya... tx
jika kepingin mengirim gambar otomatis antar sheet bagaimana boss
ReplyDeleteMisalkan ada angka x pada sel A1. Bagaimana memberikan fungsi pada sel lain misalnya pada sel B1 bahwa bila x>=80 tulis A, bila 66<=x<80 tulis B, bila 54<=x<66 tulis C, bila 40<=x<54 tulis D, dan bila x<40 tulis E. Terima kasih sebelumnya.
ReplyDeletethanks ya om, ilmunya, jadi semangat belajar xl nih
ReplyDeletenice post, really help :)
ReplyDeleteboss, tolong saya dikirimkan contoh materi tips lookup dengan banyak kriteria.. saya klik linknya sudah tidak ada lagi file nya. alamat email the.uwam@rocketmail.com..thx
ReplyDeletekalo ada table
ReplyDeleteA B
1 mangga 5000
2 pisang 2000
3 nanas 4000
4 mangga 4000
5 pisang 3000
6 mangga 4500
saya ingin mencari harga mangga termahal ?
Gan, koq ane coba tp ketnya #value!, tuh knp y???
ReplyDeletehelp me.....!
ReplyDeletepara master dan professor kok ane coba terapkan rumus diatas hasilnya ada yg salah ya....kesalahan mulai pada baris ke-7, 8, 10, dst bahkan ada yang error "#N/A"
------
kronologis :
ane mau menampilkan hasil laborat seorang pasien, ada 39 pasien. pemeriksaan laboratnya sangat banyak, klo di excel sampai 1191 cell. kriterianya adalah no. register pasien dan jenis pemeriksaan laborat. kemudian ane terapkan rumus diatas dan sukses... tapi ternyata kok muncul masalah yang ane tulis diatas--tidak semua hasilnya benar... :'(
mas, terima kasih....
ReplyDeletesangat-sangat-sangat membantu pekerjaan saya........
skalian saya mau nanya mas, apa bisa selain fasilitas filter data untuk menyaring data di current worksheet sesuai kategori yang kita inginkan,qt menggunakan fungsi2 seperti vlookup atau index+match??
misalkan saya ingin membuat ledger/data (kumpulan data) berdasarkan nama yang diambil dari sheet data entry, dimana nama2 itu akan seringkali muncul setelah nama2 lain...
contoh:
1.yuni
2.andi
3.gladis
4.jhony
5.yuni
5.yuni.
6.gladis
7.gladis
8.jhony
9.dst........
saya inginkan berdasarkan kolom nama itu, semua data2 yang ada dikolom2 sebelahnya mengikut apabila saya mengambil kategori berdasarkan nama...mohn pencerahannya...Gbyu all
Semoga membantu....
ReplyDelete={MAX(IF(A1:A6="MANGGA",B1:B6))}
akhiri dengan shift+ctrl+enter
Salam Jufri Kaltim
nice post gan
ReplyDeletekalo mau atur range lookup gimanya ya?? pls help me >.<...misalnya data formula saya sampai 500..lebih dari 500 kolom dia tidak keluar lagi formulanya..pls help,,thx before
ReplyDeleteapakah berbagai kriteria ini bisa di kombinasikan untuk sumif karena hasilnya adalah angka dan ingin saya jumlahkan
ReplyDeleteDear All, salam gabung
ReplyDeletePara Juragan Xl-Mania, ane mohon bantuanya, ane ada problem yang menurut ane lumayan, ane punya tabel yang di print dinamnis sebanyak 6 kali print, tetapi row dan kolom yang akan di print dinamis berubah terus setiap kali di refresh, hepl me please..by Macro ya..
Terima kasih sebelumnya atas bantuanya
Matzen
Dear mas abimono,
ReplyDeleteSeneng banget ketemu sama artikel ini, saya sudah coba contoh di atas, berhasil untuk data2 yang masuk di kriteria di atas, tapi saat saya mencoba dengan kriteria diluar range di atas (mis : bbb, xxx, jeruk, lebah) gak masuk di daftar range array, harusnya keluar pesan error atau #N/A, tapi ini tidak hasil yang keluar adalah "internet", ada pencerahan untuk hal ini mas abi? sebab kasus saya serupa sekali dengan contoh ini, thanks