VLOOKUP

VLOOKUP is a useful Excel function that is used to find data in an array.

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

Imagine a scenario where you have a reference table of student information, containing names, UnityIDs, and their class year.

Now, imagine you’re working as a TA and trying to input grades, but you are only given the student’s name and not their UnityID. It would be a hassle to look up each student’s UnityID one at a time, but with VLOOKUP, you can use your reference table to easily find that information.

The VLOOKUP syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Here, we use the student’s name that is entered in cell F3 as the first input to the VLOOKUP function.

table_array_ The next input is A2:C6 – the reference table. Note that the reference table should include both your input and output values (ie, it should be A2:C6, not just A2:A6 or C2:C6).

col_index_num: The next input is the column number that contains the value we want to return. Note that the column numbers start at 1, so when looking at our reference table A2:C6, column A is 1, column B is 2, and column C is 3.

[range_lookup] The final input is a boolean: TRUE for approximate search, FALSE for exact search. In the majority of cases, you want to choose FALSE.

Obviously in our small example, it may be simpler to input the data ourselves. But you can imagine cases where you may have hundreds of rows of data, spanning multiple worksheets and workbooks, when VLOOKUP then becomes very useful.

Some important nuances to VLOOKUP:

You must lookup data from left to right: you cannot search for a value in column C and return a value in column A.
Ensure the range you enter for the reference table spans the whole table: input and desired output.
Remember to use static cell referencing for the reference table if you wish to copy-paste your VLOOKUP formula.
If range_lookup is TRUE or left out, the first column needs to be sorted alphabetically or numerically. If the first column isn’t sorted, the return value might be something you don’t expect. Either sort the first column, or use FALSE for an exact match.

The official Microsoft support website was referenced for this content: https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1 It contains more information and examples if you wish to learn more.