Note: the array formula above looks up the salary of James Clark, not James Smith, not James Anderson. Use INDEX, MATCH and EXACT in Excel to perform a case-sensitive lookup.ĭo you want to look up a value based on multiple criteria? Use INDEX and MATCH in Excel to perform a two-column lookup. As a result, the VLOOKUP function returns the salary of Mia Clark (first instance). For example, the VLOOKUP function below looks up MIA (cell G2) in the leftmost column of the table.Įxplanation: the VLOOKUP function is case-insensitive so it looks up MIA or Mia or mia or miA, etc.

The VLOOKUP function in Excel performs a case-insensitive lookup. For example, take a look at the VLOOKUP function below.Įxplanation: the VLOOKUP function returns the salary of Mia Clark, not Mia Reed. If the leftmost column of the table contains duplicates, the VLOOKUP function matches the first instance. No worries, you can use INDEX and MATCH in Excel to perform a left lookup. The VLOOKUP function only looks to the right. Note: in this example, the VLOOKUP function cannot lookup the first name and return the ID. If you change the column index number (third argument) to 3, the VLOOKUP function looks up the first name and returns the salary. For example, the VLOOKUP function below looks up the first name and returns the last name.Ģ. The VLOOKUP function always looks up a value in the leftmost column of a table and returns the corresponding value from a column to the right.ġ. Note: always sort the leftmost column of the red table in ascending order if you use the VLOOKUP function in approximate match mode (fourth argument set to TRUE). The value 2 (third argument) tells the VLOOKUP function to return the value in the same row from the second column of the red table. In this example, this will be the value 80.ģ. If the VLOOKUP function cannot find the value 85 in the first column, it will return the largest value smaller than 85. Fortunately, the Boolean TRUE (fourth argument) tells the VLOOKUP function to return an approximate match. There's no value 85 in the first column.Ģ. The VLOOKUP function below looks up the value 85 (first argument) in the leftmost column of the red table (second argument). Let's take a look at an example of the VLOOKUP function in approximate match mode (fourth argument set to TRUE).ġ. Instead of returning the salary, the VLOOKUP function below returns the last name (third argument is set to 3) of ID 79. If the VLOOKUP function cannot find the value 53 in the first column, it will return a #N/A error.ģ. Note: the Boolean FALSE (fourth argument) tells the VLOOKUP function to return an exact match. The value 4 (third argument) tells the VLOOKUP function to return the value in the same row from the fourth column of the red table.

How to use vlookup in excel sample