Pengganti vlookup >> index match
Sudah kenal vlookup?
itu lho sebuah fungsi keren dari excel. VLOOKUP berfungsi untuk memunculkan nilai dari kolom secara vertikal (row) yang menjadi nilai referensi.
Dengan VLOOKUP kita bisa mengambil nilai yang merupakan keterangan nilai referensi yang masih satu ROW.
Tapi tunggu dulu, ternyata VLOOKUP ada kelemahannya lho…
VLOOKUP ini mengambil nilai referensi yang merupakan kolom paling kiri dari tabel referensi..
Wah pasti repot yah kalo kita memiliki kebutuhan yang berbeda. Alhamdulillah ada index match,,,yuks pelajari…
INDEX($D$4:$D$7;MATCH(A12;$A$4:$A$7;0))
dimana
$D$4:$D$7 (data D4 – D7 ) adalah datanya,
A12 adalah nilai referensi yang mau dicari ke table yang dimaksud
$A$4:$A$7 ( data A4-A7) adalah semua nilai referensi
Keterangan
- VLOOKUP function to search the first column of a range of cells, and then return a value from any cell on the same row of the range.
- Formula: =
- VLOOKUP(Lookup_value, Table_array, Col_index_num, Range_lookup))
- Lookup_value – The value you want to find in the lookup value column
- table_array – The table range containing columns for both the lookup and return values
- Col_index_num – The index number for the column containing the return values
- Range_lookup – The type of match: Nearest Less Than (TRUE), or Exact (FALSE) [optional]
- Formula: =
- INDEX returns the value at the intersection of a row and column in a given range.
- Formula: =INDEX(Array, Row_num, Column_num)
- Array – The range of cells
- Row_num – The row to return data from
- Column_num – The column to return the data from [optional]
- Formula: =INDEX(Array, Row_num, Column_num)
- MATCH returns a position of an item in an array that matches a value.
- Formula: =MATCH(Lookup_value, Lookup_array, Match_type)
- Lookup_value – The value you want to find in the lookup value array
- Lookup_array – The range containing lookup values
- Match_type – Exact (0), Nearest Less Than (-1), or Nearest Greater Than (1) [optional]
- Formula: =MATCH(Lookup_value, Lookup_array, Match_type)
No comments yet.
-
Archives
- February 2014 (1)
- January 2014 (2)
- October 2013 (2)
- September 2013 (2)
- May 2012 (5)
- October 2010 (1)
- September 2010 (2)
- June 2010 (3)
- April 2010 (2)
- March 2010 (2)
- February 2010 (1)
- October 2009 (1)
-
Categories
-
RSS
Entries RSS
Comments RSS
Leave a Reply