What is a vlookup excel?

Understanding vlookup in excel:

A vlookup is a function in Excel that allows you to look up specific values in a spreadsheet and return the corresponding corresponding values. This makes it easy to organize your data and quickly find the information that you are searching for.

VLOOKUP stands for “vertical lookup”, because it allows you to search for values across columns instead of down rows like traditional lookups.

To perform a vlookup, simply enter the lookup value in the first column of your spreadsheet, then specify which column you want returned by entering either a number or a cell reference into the second argument of the function. You can also specify additional options such as whether or not an exact match must be found, and if comparisons should be made using text or numbers.

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).

Use the VLOOKUP function to look up a value in a table.

Syntax 

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

For example:

  • =VLOOKUP(A2,A10:C20,2,TRUE)
  • =VLOOKUP(“Fontana”,B2:E7,2,FALSE)
  • =VLOOKUP(A2,’Client Details’!A:F,3,FALSE)

How to get started

There are four pieces of information you’ll need for VLOOKUP syntax construction:

  1. The value you want to check, also known as the lookup value.
  2. The column where the lookup value is found. VLOOKUP requires that the lookup value be in the first column of your range. If your lookups value is in cell C2, for example, you should start your range with C.
  3. The column number in the range that contains the return value. If you want to count from B2:D11, for example, you should start counting with B and continue until D.
  4. Finally, if you want to make an approximate or exact match of the return value, choose TRUE or FALSE, respectively. If you don’t provide anything, the default value will be true or an approximate match.

Now put all of the above together as follows:

=VLOOKUP(lookup value, range with the lookup value, column number in the range with the return value, Approximate match (TRUE) or Exact match (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.