At first sight, this article may look long-winded to you, but trust me, with a little concentration and patience, this article alone can help you master all the difficult excel functions like offset, indirect, index, match, etc. So, if you are ready to take the plunge, here is the magic article.
I am using Microsoft Office 2016 for this tutorial. If you want to practice the tutorial yourself, I have shared the Excel Workbook at the end of this article. It will definitely help you understand this tutorial easily. Please download it.
If you want to look up some value using two criteria instead of just one, then it cannot be done using simple VLOOKUP and SUMIF functions alone, as they take into account only one condition. In this situation we should use a 'Two-dimensional Lookup'. It is like doing a multiple criteria vlookup.
Let’s take a simple example.
Suppose there is a list of airlines along with their respective fares for both Economy and Business class.
But we just want to know the airfare of say 3 airlines for both the classes.
If the list is short, then you can easily spot it. However, when you have a long list, then this could take hours when it’s just a few minutes job.
Now in order to fill up this table, we need to lookup value using two conditions:
- Which airline?
- Which Class?
If we use the plain Vlookup function we will manually have to change the formula to move from one column to the next. For e.g. if we use the following Vlookup formula in cell B5
=VLOOKUP(B$4,$F$4:$H$13,2, FALSE) then it would work fine as long as we want the values from the second column of the list i.e. Economy class fares. But if we drag this formula down, then it will continue taking values from the second column when we want values from the third. So we manually will have to change column number from 2 to 3 for the business class as shown in the diagram below:
Similarly, if we use the SUMIF function, you will need to change the cell reference manually from Column G to Column H.
To get around this problem and to use the same formula throughout, we have the following options available:
1. VLOOKUP along with MATCH function
Here, VLOOKUP would take care of the first condition, while the MATCH function would handle the second criteria.
So using our example above, the match function would help us locate the right column and VLOOKUP the right row.
The match function fits in well with VLOOKUP as it searches for a value in an array and returns the relative position of that item.
The syntax for the MATCH function is:
MATCH( value, array, match_type )
So in our example,
The value represents our second condition i.e. “Economy” or “Business” Class.
Array: Here we need to take a cautious approach. For VLOOKUP to work fine, we want it to take column 2 for the “Economy” class and 3 for “Business”. So, we should choose such a range wherein Economy is placed at number 2 and business at 3. So, we can choose the range F4 to H4.
Just a word of warning - the MATCH function can only work with a single row or column range. So, we can not choose F4 to H13 as our range.
Match type: This can be “True” or “False”.
False represents the exact match therefore we need to enter false. So now, if we enter MATCH ($A5,$F$4:$H$4, FALSE) in cell B5 it would return 2, which is what we require.
And, if we use this along with VLOOKUP then it will search the relevant airline and MATCH would take care of the respective class. So, let’s type in
in cell B5. If you use an absolute reference, ($F$4:$H$13), instead of a relative reference (F4:H13), it will be easier to copy the formula to other cells.
So it is picking up the right fare and we can simply drag it down without making any manual adjustments. Isn’t that great!
2. SUMIF along with OFFSET and MATCH function
Unlike Vlookup, the match function would not work alone with the SUMIF function as it returns a relative row or column number whereas for SUMIF we need a range. To get a clear picture, let’s compare the syntax for SUMIF and VLOOKUP functions.
The syntax for the SUMIF function is:
The syntax for the VLOOKUP function is:
In the VLOOKUP + MATCH example above:
Lookup value was the respective airline.
Table array was the entire list of airlines.
The column index was the column number.
Range lookup was False as we wanted an exact match.
And, we used the MATCH function to pick up the right column.
Now let’s start building a SUMIF function and see where we get stuck.
The range is like the “Table array” in the VLOOKUP function so we can choose $F$4:$H$13 or you can simply choose $F$4:$F$13.
A criterion is like a Lookup value in the VLOOKUP function so this will take care of our first criterion i.e. airline. Sum_range is again like the column index. But instead of column number we need to choose the correct column range for e.g. for Economy class we want to choose the range $G$4:$G$13 and $H$4:$H$13 for business class. So, we need to search for a function that could give us the right range. Luckily, the OFFSET function can do this job for you. The OFFSET function returns a reference to a range.
The syntax for the OFFSET function is:
OFFSET( range, rows, columns, height, width )
Here, the last two are optional. In our example, the range would be the list of airlines i.e. $G$4:$H$13.
Just a word of caution - 0 in offset function represents the first row, unlike the VLOOKUP function. Hence for rows, we would choose 0. To select the right column we can fit the MATCH function into the OFFSET. Since we chose our range from G instead of F therefore Economy becomes the first column. But for the Economy class, we want the MATCH function to return 0 as 0 represents the first column. However, for the MATCH function Economy is positioned at number 1 in the range, $G$4:$H$4 and so it would return 1. But we want 0 so putting -1 at the end will do the trick.
Now, putting everything together in cell B5, the complete formula would look like
SUMIF would pick up the right airline while the offset would take care of the class as shown in the diagram below:
3. INDEX along with MATCH function
INDEX and MATCH functions complement each other perfectly. INDEX function can return a value of a specific cell and the MATCH function would help it locate the position of that cell.
The syntax for the INDEX function is:
In our example, the array would be the list.
And we can use the MATCH function to pick up the right row number and column number.
So this time, the complete formula is:
Here, the first match function tells the Index function which airline to take and the second match will help it figure out the right class. The figure below shows the result:
4. INDIRECT function along with ADDRESS and MATCH function
Like the INDEX and MATCH function, we can use the INDIRECT function along with ADDRESS and MATCH to look up values based on two different pieces of information.
The syntax for the Indirect function is:
INDIRECT( string_reference, ref_style)
String_reference is a textual representation of a cell reference.
Ref_style is optional.
The INDIRECT function of Excel allows you to construct a formula by referring to the contents of a cell, rather than the reference cell itself.
For example, if cell A5 has a value of 6 then the formula =INDIRECT(“$A$5”) would return 6.
So, continuing with our example, we know the final formula in cell B5 should look like:
But we want this formula to be flexible enough so that as we move from cell B5 to B6, it automatically converts the formula to =INDIRECT($H$7).
The ADDRESS function allows you to do this.
The syntax for the ADDRESS function is:
Here the last three entries are optional.
So, in our example, ADDRESS(7,7) would return $G$7 which is exactly what we want.
Now, we just need to find a way to be able to pick up the right row and column number. And, I am quite sure by this time; you know which function can help us do that. Yes, it is the powerful “MATCH” function.
Now we have all the formulas we need to enable us to find the right fare. So if we put them together in cell B5 our end formula should look like:
We have added 3 and 5 just so it takes the correct row and column number.
5. SUM(IF()) Array formula
Array formulas can also help us look up values based on two criteria. The array formula uses simple logic.
In cell B5, type the following formula:
It just says that sum only if our list matches the relevant airline and class. This multiplication sign (*) acts like “AND” in an array formula.
Moreover, to get the result, hold Ctrl + Shift, and press the Enter key rather than just ENTER.
Curly brackets will be automatically added to the formula but it won’t work if you type them yourself. Even if you want to edit the formula later, you have to press CTRL+SHIFT+ENTER together, otherwise, it won’t work.
6. Using Pivot Table
If you are not very comfortable with excel formulas, then you can also obtain values based on two criteria using pivot tables.
To create a pivot table, place the cursor in cell A4 or wherever you want to display the table. And then select the PivotTable from the Insert menu as shown below.
Next click on the select button as shown below
And then select the cells that contain the list - cells $F$4:$H$13 in our example. Again click on the button to the right of the selector dialog. In Create PivotTable, click OK.
You would see the PivotTable Fields section to the right. Arrange the fields by dragging them as shown below.
You will see a Pivot table as shown below.
Let’s clean this up a bit. Clicking on the drop-down on the row labels in Cell A4, select the 3 airlines we need.
To do this, first, deselect everything by clearing the check on "(Select All)" Option. Then select each of the airlines we require and then click on OK.
And to remove the grand totals, select any cell in the pivot table. Click on Design Menu under PivotTable Tools. Then click on the Subtotals dropdown menu and click on Do Not Show Subtotals.
Again click on Design Menu under PivotTable Tools. Then click on the Grand Totals dropdown and click on the 'On for Columns Only' Option.
Now you will see a neater version of the Pivot table. There some steps left to arrange the fields properly.
For this, we have to swap the position of Columns and Rows in PivotTable. Just drag the field buttons as shown below.
Finally, drag the fields to order it as required.
And it is done.
If this has helped you, please do consider sharing it with your colleagues and friends.