Lookup Functions are an easy way to pull a piece of information from large data sets. In this article, we will look at three different lookup functions and how they work.


Here is the data set we will be working with to demonstrate how to use these functions. You can also download this sheet and practice for yourself:




VLOOKUP


Vertical lookups (vlookups for short) are the basic function of putting in an input and a series of conditions to calculate a certain output. The formula looks like:


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


Let's go through these steps one by one and explain how each variable affects the output of this formula. In the following example, we will be attempting to output Abby's hair color based on her number, and will be using these cells within the practice sheet.



  • Lookup Value: This is the value of the information you know that will be used to find the information that you are searching for. Since we are trying to find Abby's hair color based on her number in the set, this value will simply be 1. We can also use a referenced cell that contains the lookup value. A10 in the above case
    • =VLOOKUP(1,     OR    =VLOOKUP(A10,
  • Table Array: This variable is designated for the table that contains the information. You will need to use the cell references to indicate this. For our example's purposes, our table array will be A1:E6. IMPORTANT: The column that the lookup value is in must be the LEFTMOST column in the table array. 
    • =VLOOKUP(1,A1:E6,        OR       =VLOOKUP(A10,A1:E6,
  • Column Index Number: This variable finds the column that you are trying to reference, presented in a numerical value based on your table array. For our above example, the column numbers would be
    • Number = 1
    • Name = 2
    • Eye Color = 3
    • Hair Color = 4
    • Height = 5

So if we are trying to find Abby's hair color, we will want the function to look in column 4.

    • =VLOOKUP(1,A1:E6,4,         OR             =VLOOKUP(A10,A1:E6,4

  • Range Lookup (Optional): This is a binary variable, only having two valid values: True (1) and False (0). In simple terms, True will find an approximate match to your lookup value, while false will require an exact match to your lookup value. In almost all cases we will want an exact match, so always pick False. If you exclude this input, it will default to False.

    • =VLOOKUP(1,A1:E6,4,0)    OR   =VLOOKUP(A10,A1:E6,4,FALSE)   OR    =VLOOKUP (A10,A1:E6,4)




INDEX & MATCH


INDEX and MATCH, when used together, try to achieve the same thing as VLOOKUP but from a different approach. One of the main limitations of VLOOKUP is that your output value must be to the right of the lookup value in the table. But what if you had height as your lookup value and your desired input was the person's name? VLOOKUP would not work in this case, which leads us to INDEX and MATCH.


=INDEX(array,row_num,[column_num])


=MATCH(lookup_value,lookup_array,[match_type])


Let's try to find which person (by name) is a height of 72 inches.


We'll start with MATCH, taking each input step-by-step. What the MATCH function does is it finds the row in which the lookup value exists in a single column array.

  • Lookup Value: This is the value of the information you know that will be used to find the information that you are searching for. Since we have a height of 72 for our lookup value, we will use that or reference a cell containing that.
    • =MATCH(A14,
  • Lookup Array: This is simply the column that our lookup value exists in. In our case, this will be E1:E6.
    • =MATCH(A14,E1:E6,
  • Match Type (Optional): For our purposes of combining INDEX and MATCH, we must use an exact match for this variable. Either leave blank or use 0.
    • =MATCH(A14,E1:E6,0)


After inputting this formula, the value returned is 5. What this tells us is that in the array that we specified, our lookup value of 72 exists in the 5th row. 

Coincidentally, this is one of our required inputs for the INDEX function. Now let's take the INDEX function step by step.

  • Array: This will be our full table A1:E6.
    • =INDEX(A1:E6,
  • Row Number: The row we want to index. Literally just put your MATCH function into this section.
    • =INDEX(A1:E6,MATCH(A14,E1:E6,0),
  • Column Number: Even though this is bracketed to optional, this will denote the column number of the value we are trying to find based on our match. Use the same values as the column numbers from the VLOOKUP section. Since we're trying to find a name, the column number will be 2. 
    • =INDEX(A1:E6,MATCH(A14,E1:E6,0),2)


Putting this formula into our practice area, we get our desired output, David.


XLOOKUP


The XLOOKUP function takes the ease of VLOOKUP and the advanced functionality of INDEX and MATCH and puts them into one easy-to-use formula.


=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])


We will not be using the match mode or search mode variables.


Let's go back to the example we used in the INDEX MATCH lesson and try to find the person who is 72 inches tall. 


We will take the formula step-by-step:

  • Lookup value: This is the value of the information you know that will be used to find the information that you are searching for. Since we have a height of 72 for our lookup value, we will use that or reference a cell containing that.
    • =XLOOKUP(A18,
  • Lookup array: The array that the lookup value exists in, usually the column of the table where the lookup value is located. In our example, we'll use E1:E6.
    • =XLOOKUP(A18,E1:E6,
  • Return array: The array that the output exists in, usually the column of the table where the return value is located. In our example, we'll use B1:B6 since we're trying to find a name.
    • =XLOOKUP(A18,E1:E6,B1:B6)
  • If Not Found: This can be anything we want the function to return if the function fails to find a match. You can simply put anything like "Not Found" or "N/A", or other relevant information based on your project. For this example, we'll just leave this blank.


Inputting this formula now gives us our desired result, David.