Lookup and Reference Functions (Advanced Level – ★★★)
In this final section, we will focus on functions that allow you to look for specific data points and refer to them.
Take a look at the “Schedule” worksheet.
The COLUMN function in Excel returns the column number of a given cell.
Let’s try to assign specific dates for each quiz. For example, you may want the quizzes to be held every Monday. This means that the first quiz date should be offset by 1 week or 7 days for each succeeding quiz date.
You can use the column number to multiply the 7 days offset for each week like this:
Two (2) is subtracted from the column number so that the sequence starts at 1.
You can also get this result using the much simpler “=B2+7” since you are only adding a fixed number of days to each date. 🤔
But, using the COLUMN function, you can create complex patterns.
Take this pattern for example:
The quizzes are still held every Monday. But every third week, they are held on Wednesday instead.
Here is the formula for this pattern:
The MOD function in Excel returns the remainder after a number is divided by a given divisor. It’s part of the Math & Trig group of functions.
This group includes other fun functions such as ABS which returns the absolute value of a number and ROUND which rounds a number to a specified number of digits.
Learn more about the function groups towards the end of this article!
Next, take a look at the ROW function. It works exactly like COLUMN but it returns the row number instead.
In this next example, you will assign the seating plans. You can try different seating arrangements using the ROW function.
Assume R1C1 is the seat closest to the teacher’s desk.
1. You can have the students seated one seat after another and in two columns:
2. Or they can sit in rows of 3 and columns of 2
3. You can also sit them in the farthest rows:
4. Or in the farthest columns:
Manually creating seating patterns for small sets like this one is easy. But a formula like those shown above definitely helps especially for larger sets like 50, 100, or even more.
The COLUMN and ROW functions are rarely used on their own. Like IF and IFS, you use them with other functions to change how the formula is calculated.
Now, open up the “Lookup” worksheet.
In the next few examples, you will create a search feature that allows students to look up their names. They can then see their scores from past quizzes and their assigned seats for the next quizzes.
To start, you will use the MATCH function. It searches for a specified item within a given range of cells. It then returns the relative position of the first match.
Syntax: =MATCH(lookup_value, lookup_array, [match_type])
- The lookup_value is the item you want to search for. So, set this to cell B2.
- The lookup_array is the range or table array where you want to search. Use F2:F7 from the “Class List” worksheet.
- For the match_type, set this to zero so that the function searches for an exact match. (Learn more about MATCH and the different match types in this article)
The formula then becomes:
However, it only works correctly if the name is entered exactly as it is written in Column F of the Class List.
To fix this, you can use the asterisk “*” wildcard character so that searching for either first or last name works.
You can also enclose the formula in an IFNA function. This way, if the formula cannot find the given name in the table, it will return a phrase like “No result found”.
The INDEX function retrieves a value from a given table array based on the provided row and column numbers.
Syntax: =INDEX (array, row_num, [col_num])
Similar to the MATCH example, you need to specify where the range or array lookup is.
For row_num, you can use the earlier MATCH result at Cell B5. Then for col_num, use 1 for the First Name:
And set col_num to 2 for the Last Name.
Just like that, you have a working search 🔍 formula!
This is just a small example of the countless possibilities using the INDEX and MATCH combination. Click here for more examples!
The VLOOKUP function in Excel works similarly to the INDEX and MATCH combination. It is faster to set up but it is less versatile. VLOOKUP also only works if your lookup array is at the leftmost of the reference table.
Syntax: =VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
This time, you will use the First Name result (cell B6) as the lookup_value. Use this and VLOOKUP to retrieve the given student’s scores from the “Quiz Scores” worksheet.
For the seat assignment, use the Last Name result followed by the asterisk wild character.
The last function that you will be learning about today is also one of the most powerful in Excel.
INDIRECT allows you to specify cell references using text strings.
SYNTAX: =INDIRECT(ref_text, [a1])
For example, instead of typing “=A1”, you can type “=INDIRECT(“A”&1). This means you can dynamically change references.
Let’s take the INDEX & MATCH formula you used to retrieve the Last Name. You can get the same result using this formula:
The INDIRECT function opens up so many possibilities with dynamic references in Excel. I highly this article for an in-depth tutorial on INDIRECT.