Ada yang harus kita perhatikan ketika bekerja dengan fungsi (function) dalam OpenOffice.org. Kebetulan ini yang baru dialami, mungkin masih banyak lagi yang harus dieksplorasi sehingga akan diperoleh sesuai dengan harapan kita.

Salah satu function yang cukup merepotkan dan perlu diperhatikan adalah fungsi :

=vlookup() –> Vertical Look Up Function, untuk menghasilkan informasi berdasarkan data yang tersedia.

Fungsi ini tidak akan sederhana, jika kita ingin menghasilkan informasi secara “akurat”.  Jadi option yang penting untuk diperhatikan ketika memakai vlookup function adalah “accuracy”.

Jadi, format vlookup function secara utuh adalah sebagai berikut :

=VLOOKUP( what to search for , where to search , what result to give , accuracy )

what to search for
This can either be a value or, as here, a cell reference. In our example we wanted to search for something related to “Peter”, and we got that that value from cell E3. We would have excactly the same result if we replaced E3 with Peter, like this: =VLOOKUP(“Peter”,A1:B100,2,0)

where to search
This is the cell reference to the table we want to work with. In this case it is the area A1 to B100. As you see, the names are given in the left column, and the =VLOOKUP() formula will always search in the left column of any table. This is important.

The reason for typing B100, which is obviously too far down, is to make sure that if we add more data here, it will be included in the formula. Data enteret in cell A101 and B101 will not be considered by the formula. It is, by the way, not required that you include the column headings, like I’ve done here.

what result to give
If Calc is able to find what you wanted to search for, where you wanted to search for it, you need to specify exactly what information you want in return. In this case, we specified that we wanted the related information from column number 2 in the table. Notice that the number is totally unrelated to the cell reference itself — of the same table was placed in the columns F and G, the reference number for the feedback would still be number 2, as it refers to the table you defined in the green area.

accuracy
OK, to be more accurate it really means sorted or not sorted… If it says “1“, it means that the list is sorted, and will give you the closest match to what you searched for. You will probably never use that in you entire life, so I suggest that you consequently use “0” instead, which requires a perfect match. If it doesn’t find a perfect match, it will show an error message: “#N/A“.

(mengacu ke OpenOfficeSchool.org, dengan penyesuaian tanda oleh penulis…)

Yang dibahas disini adalah masalah “accuracy“, karena ada “ketidakpuasan hasil” yang diperoleh apabila tidak memakai option “accuracy” .

Supaya tidak panjang lebar, lihat contoh hasil di bawah ini (silahkan pelajari…😉 ) :

Tabel data :

B C
1 Nama Nilai
2 Agus 4
3 Ahmad 8
4 Andi 13
5 Dedi 12
6 Febi 2
7 Juli 7
8 Juniar 1
9 Marni 3
10 Meilani 5
11 Oktaviani 10
12 Septiani 9

Tabel hasil :

1. Untuk kolom G, memakai fungsi sebagai berikut =vlookup($F2,$B$2:$C$12,2)

2. Untuk kolom H, memakai fungsi sebagai berikut =vlookup($F2,$B$2:$C$12,2,0)

F G H
Nama Hasil Olah 1 Hasil Olah 2
1 Agus 4 4
2 Ahmad 8 8
3 Andi 13 13
4 Budi 13 #N/A
5 Dedi 12 12
6 Febi 2 2
7 Juli 7 7
8 Juniar 1 1
9 Marni 3 3
10 Meilani 5 5
11 Oktaviani 10 10
12 Septiani 9 9

Silahkan Anda simak …

Jika saya ada kekeliruan, mohon kasih comment… terima kasih.