Manuals/calci/VLOOKUP

VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup)

where,

• lkup_val is the value to be matched from an array or first column of the table,
• table_arr refers to two or more columns of data values,
• column_index_num is the column number in the table from which the matching value should be returned, and
• range_lkup is an optional logical value that specifies whether to find an exact match or approximate match of the mentioned lookup value.

VLOOKUP() looks up for a value in the first column of a table array and gives a value in the same row from specified column in the table array.

Description

VLOOKUP(lkup_val, table_arr, column_index_num, range_lkup)

• The 'V' in VLOOKUP stands for vertical. This function looks the value column wise.
• Arguments containing text values entered as a part of command, should be enclosed in quotes (e.g. "JOHN").

• VLOOKUP function is not case-sensitive.
• If lkup_val is less than 'smallest value in first column of table', Calci displays #NULL error message.
• If column_index_num is 1, Calci returns the value from the first column in table array. If column_index_num is 2, Calci returns the value from the second column in table array and so on.
• For column_index_num < 1 or column_index_num > 'number of columns in array', Calci returns #NULL error message.
• The values in first column should be arranged in ascending order for correct results.
• Argument range_lkup can be TRUE or FALSE.
• If range_lkup is TRUE or Omitted, Calci returns exact or approximate match of the value.
• Approximate value is the largest value that is <= lkup_val.
• If range_lkup is FALSE, Calci searches only for the exact match. If exact match is not found, Calci returns #NULL error message.
• For invalid arguments, Calci displays #NULL error message.

Examples

Following table shows the use of VLOOKUP function -

Number	Square	Cube
1	1	1
2	4	8
3	9	27
4	16	64
5	25	125
6	36	216

=VLOOKUP(3,A2:C7,2) : Looks up for '3' in Number Column and returns value from Square column 
in the same row. Returns 9 as a result.
=VLOOKUP(5,A2:C7,3,FALSE) : Looks up for exact match for '5' in Number Column and returns value from 
Cube column in the same row. Returns 125 as a result.
=VLOOKUP(5.5,A2:C7,2,TRUE) : Looks up for approximate match for '5.5' in Number Column, 
matches the nearest value '5' and returns value from Square column in same row. 
Returns 25 as a result.

Related Videos

VLOOKUP

See Also

• LOOKUP
• HLOOKUP
• MATCH

References

• VLOOKUP
• Find String