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.
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.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 look up value using two conditions:
1) Which airline?
2) 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 business class as shown in the diagram below:
Similarly, if we use 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 functionHere, Vlookup would take care of the first condition, while the Match function would handle the second criterion.
So using our example above, match function would help us locate the right column and Vlookup the right row.
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,
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 “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 - 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 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 vlookup will search the relevant airline and match would take care of the respective class. So, let’s type in =VLOOKUP(B$4,$F$4:$H$13,MATCH($A5,$F$4:$H$4,FALSE),FALSE)
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. Or, you can name the lookup table, and refer to it by name (Insert-Name-Define)
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 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 function.
The syntax for SUMIF function is:
SUMIF(range,criteria,sum_range)
The syntax for VLookup function is:
Vlookup(lookup_value,table_array,col_index_num,range_lookup)
In the Vlookup + Match example above:
Lookup value was the respective airline.
Table array was the entire list of airlines.
Column index was column number.
Range lookup was False as we wanted an exact match.
And, we used 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 Vlookup function so we can choose $F$4:$H$13 or you can simply choose $F$4:$F$13.Criteria is like Lookup value in 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 a function that could give us the right range.Luckily, 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, 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 Vlookup function. Hence for rows we would choose 0. To select the right column we can fit match function into the offset. Since we chose our range from G instead of F therefore Economy becomes the first column. But for Economy class, we want match function to return 0 as 0 represents first column. However, for 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($F$4:$F$13,B$4,OFFSET($G$4:$H$13,0,MATCH($A5,$G$4:$H$4,FALSE)-1))
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 Match function would help it locate the position of that cell.
The syntax for INDEX function is:
INDEX(array,row_num,column_num)
In our example, 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:
=INDEX($F$4:$H$13,MATCH(B$4,$F$4:$F$13,FALSE),MATCH($A5,$F$4:$H$4,FALSE))
Here, first match function tells the Index function which airline to take and 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 index and match function, we can use indirect function along with address and Match to look up value 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.
Excel’s Indirect function allows the creation of a formula by referring to the contents of a cell, rather than the cell reference itself. For example, if cell A5 has value 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:
=INDIRECT($G$7)
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:
ADDRESS(row_num,column_num,abs_num,a1,sheet_text)
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 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:
=INDIRECT(ADDRESS(MATCH(B$4,$F$4:$F$13,FALSE)+3,MATCH($A5,$F$4:$H$4,FALSE)+ 5))
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 value based on two criteria.The array function uses simple logic.
In cell B5, type the following formula: =SUM(IF(($F$5:$F$13=B$4)*($G$4:$H$4=$A5),$G$5:$H$13))
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) 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 wizard from the data menu as shown below.
Once the wizard is there, click Next
And then select the cells that contain the list - cells $F$4:$H$13 in our example.
And then click “Layout”
And choose the following layout by dragging cells.
As you enter “OK” you will see a table as shown below.
Let’s clean this up a bit.From the drop down airlines menu, choose the 3 airlines we need.
And to remove the grand totals, select any cell in the pivot table. Right click and and then select “Table Options” from the popup menu and simply uncheck grand totals for columns and rows as shown below:
As you now hit “OK” you will see a neater version of pivot table.
Share This
Popularity: 88%