Excel is a powerful tool for organizing and analyzing data. It is widely used in various industries and professions, ranging from finance, marketing, and even in the creative field. As a creative professional, you may wonder how Excel can help you in your work. In this article, we will explore one of the most important Excel functions – VLOOKUP – and how it can be used to extract data from different files.
Rumus Excel VLOOKUP
VLOOKUP stands for Vertical Lookup, and it is a function in Excel that allows you to search for a specific value in one column and return a corresponding value from another column in the same row. This function is particularly useful when dealing with large datasets, where it can be time-consuming and prone to errors to manually search for and extract data. The syntax for VLOOKUP is as follows:
=VLOOKUP(lookup_value,table_array,col_index_num, [range_lookup])
The four arguments in the VLOOKUP function are:
- lookup_value: The value that you want to look up.
- table_array: The range of cells where you want to look up the value and return the result. This range must include the lookup_value and the column that contains the result that you want to return.
- col_index_num: The column number in the table_array from which you want to return the result. The first column in the table_array is 1, the second column is 2, and so on.
- range_lookup: An optional argument that indicates whether you want an exact match or an approximate match. If this argument is omitted, the function will perform an approximate match by default.
Mengambil Data dari File Lain
One of the most powerful features of VLOOKUP is its ability to extract data from different files. Let’s say you have two Excel files – file A and file B – and you want to extract data from file B and insert it into file A. Here’s how you can do it:
- Open file A and click on the cell where you want to insert the result.
- Type the VLOOKUP formula and specify the lookup_value, table_array, col_index_num, and range_lookup arguments.
- In the table_array argument, specify the full path of file B, followed by the sheet name and the range of cells that contains the data you want to extract. For example, if the data you want to extract is located in cells A1 to C10 on Sheet1 of file B, the table_array argument would be:
- Press Enter to execute the formula. The result should now be displayed in the cell where you inserted the formula.
'C:\Users\User\Desktop\fileB.xlsx'!Sheet1!A1:C10
FAQ
What is the difference between VLOOKUP and HLOOKUP?
VLOOKUP and HLOOKUP are both Excel functions that allow you to search for a value in a table and return a corresponding value from another column or row. The main difference between the two functions is the orientation of the lookup table. VLOOKUP searches vertically for a value in the first column of a table and returns a value in the same row from a specified column. HLOOKUP, on the other hand, searches horizontally for a value in the first row of a table and returns a value in the same column from a specified row.
What is the purpose of the range_lookup argument in VLOOKUP?
The range_lookup argument in VLOOKUP determines whether you want to perform an exact match or an approximate match. If you set this argument to TRUE or omit it, Excel will perform an approximate match by default. An approximate match means that Excel will search for a value that is less than or equal to the lookup_value and return the closest match. If you set the range_lookup argument to FALSE, Excel will perform an exact match and return an error if it cannot find an exact match.
Cara Membuat Rumus Excel Pada Sel
Excel tidak hanya bisa digunakan untuk menghitung data dengan rumus sederhana, namun Excel juga berfungsi untuk menampilkan data dari sheet lain. Selain itu, kamu bisa melakukan perhitungan aritmatika pada sel secara otomatis dengan membuat rumus di Excel. Berikut adalah cara membuat rumus di Excel:
- Pilih sel tempat kamu ingin menampilkan hasil perhitungan.
- Ketik tanda sama dengan (=) pada sel yang ingin kamu hitung.
- Kamu bisa menggunakan rumus matematika sederhana seperti penjumlahan (+), pengurangan (-), perkalian (*), dan pembagian (/).
- Atur urutan penjumlahan dengan kurung buka dan tutup ().
- Tekan enter setelah mengetik rumus di sel, maka hasil perhitungan akan muncul.
Sumber:
- https://www.myblog.id/rumus-excel-vlookup-mengambil-data-dari-file-lain/
- https://modulkomputer.com/2017/10/19/rumus-excel-mengambil-data-dari-sheet-lain/
- https://dehaliyah.com/cara-membuat-rumus-excel-pada-sel
- https://www.pentoncs.net/tutorial-cara-membuat-tabel-di-excel-dengan-rumus-dengan-gambar/
- https://www.neicytekno.com/2022/01/rumus-excel-untuk-menampilkan-data-dari-sheet-lain.html
