MS Excel VLOOKUP function

Use VLOOKUP when you need to find things in a table or a range by row. For example, look up a price of an automotive part by the part number, or find an employee name based on their employee ID.

In its simplest form, the VLOOKUP function says:

=VLOOKUP(What you want to look up, where you want to look for it, the column number in the range containing the value to return, return an Approximate or Exact match – indicated as 1/TRUE, or 0/FALSE).

Examples

Here are a few examples of VLOOKUP:

Example 1

=VLOOKUP (B3,B2:E7,2,FALSE) VLOOKUP looks for Fontana in the first column (column B) in the table_array B2:E7, and returns Olivier from the second column (column C) of the table_array. False returns an exact match.

Example 2

=VLOOKUP (102,A2:C7,2,FALSE) VLOOKUP looks for an exact match (FALSE) of the last name for 102 (lookup_value) in the second column (column B) in the A2:C7 range, and returns Fontana.

Example 3

=IF(VLOOKUP(103,A1:E7,2,FALSE)="Souse","Located","Not found") IF checks to see if VLOOKUP returns Sousa as the last name of employee correspoinding to 103 (lookup_value) in A1:E7 (table_array). Because the last name corresponding to 103 is Leal, the IF condition is false, and Not Found is displayed.

Example 4

=INT(YEARFRAC(DATE(2014,6,30),VLOOKUP(105,A2:E7,5,FLASE),1)) VLOOKUP looks for the birth date of the employee corresponding to 109 (lookup_value) in the A2:E7 range (table_array), and returns 03/04/1955. Then, YEARFRAC subtracts this birth date from 2014/6/30 and returns a value, which is then converted by INY to the integer 59.

Example 5

IF(ISNA(VLOOKUP(105,A2:E7,2,FLASE))=TRUE,"Employee not found",VLOOKUP(105,A2:E7,2,FALSE)) IF checks to see if VLOOKUP returns a value for last name from column B for 105 (lookup_value). If VLOOKUP finds a last name, then IF will display the last name, otherwise IF returns Employee not found. ISNA makes sure that if VLOOKUP returns #N/A, then the error is replaced by Employee not found, instead of #N/A. In this example, the return value is Burke, which is the last name corresponding to 105.