Cari dan ambil nilai dalam tabel
=VLOOKUP(lookup_value, table_array, column_index_num, [range_lookup])| Parameter | Deskripsi |
|---|---|
lookup_value |
The value to look for in the first column of a table. |
table_array |
The table from which to retrieve a value. |
column_index_num |
The column in the table from which to retrieve a value. |
range_lookup |
[optional] TRUE = approximate match (default). FALSE = exact match. |
With the order number 1005 as a lookup value in cell I4, the result is 125. VLOOKUP scans the first column of the table, matches order number 1005, an
=VLOOKUP(I4,B5:F9,3,FALSE)To retrieve a value from a given column, just provide the number for column_index_num. For example, to retrieve the first name in cell H4, we use 2 fo
=VLOOKUP(H3,B4:E13,2,FALSE) // first name
=VLOOKUP(H3,B4:E13,3,FALSE) // last name
=VLOOKUP(H3,B4:E13,4,FALSE) // email addressVLOOKUP has two match modes: exact match and approximate match. The last argument, called range_lookup, controls which match mode is used. The word "r
=VLOOKUP(value,table,col_index,TRUE) // approximate match
=VLOOKUP(value,table,col_index,FALSE) // exact matchThe formula in H6 to find Year, based on an exact match of the movie title, is:
=VLOOKUP(H4,B5:E9,2,FALSE) // FALSE = exact matchIn some cases, you will need an approximate match lookup instead of an exact match lookup. A good example is the problem of assigning a letter grade b
=VLOOKUP(C5,$G$5:$H$10,2,TRUE) // TRUE = approximate matchIt is important to understand that VLOOKUP will perform an approximate match by default. This happens because range_lookup is optional and defaults to
=VLOOKUP(F5,B5:D10,3) // approximate by default!