The VLOOKUP, HLOOKUP and INDEX-MATCH are famous and common ways to lookup a values in Excel table. But these are not the only ways to lookup values in Excel. In this article, we will learn how to use the OFFSET function along with the MATCH function in Excel. Yes you read it right, we will use the infamous OFFSET function of Excel to lookup a certain value in a Excel Table.
| =OFFSET(StartCell,MATCH(RowLookupValue,RowLookupRange,0),MATCH(ColLookupValue,ColLookupRange,0)) |
Read this carefully:
StartCell: This is the starting cell of lookup Table. Let's say if you want to lookup in range A2:A10, then the StartCell will be A1.
RowLookupValue: This is the lookup value that you want to find in rows below the StartCell.
RowLookupRange: This is the range in which you want to lookup the RowLookupValue. It is the range below StartCell (A2:A10).
ColLookupValue: This is the lookup value that you want to find in columns (headers).
ColLookupRange: This is the range in which you want to lookup the ColLookupValue. It is the range on the right hand side of StartCell (like B1:D1).
So, enough of the theory. Let's get started with an example.
Here we have a sample table of sales done by different employees in different months.
Now our boss is asking us to give the sales done by Id 1004 in the Jun month. There are many ways to do it but since we are learning about the OFFSET-MATCH formula, we will use them to lookup the desired value.
We already have the generic formula above. We just need to identify these variables in this table.
StartCell is B1 here. RowLookupValue is M1. RowLookupRange is B2:B1o (range below the startcell).
ColLookupValue is in M2 cell. ColLookupRange is C1:I1 (Header Range on the right of the StartCell) .
We have identified all the variables. Let's put them into an Excel Formula.
Write this formula in Cell M3 and hit the enter button.
| =OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0)) |
As you hit the enter button, you get the result promptly. The sale done ID 1004 in Jun Month is 177.
How does it work?
The OFFSET function's work is to move away from the given starting cell to given row and columns and then return value from that cell. For example, If I write OFFSET(B1,1,1), the OFFSET function will go to cell C2 (1 cell down, 1 cell to right) and returns it value.
Here, We have formula OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0)).
The first MATCH function returns the index of M1 (1004) in range B2:B10, which is 4. Now the formula is, OFFSET(B1,4,MATCH(M2,C1:I1,0)).
Next MATCH function returns the index of M2 ('Jun') in range C1:I1, which i 7. Now the formula is OFFSET(B1,4,7).
Now the OFFSET function simply moves 4 cells down to the cell B1 which takes it to B5. Then the OFFSET function moves to 7 left to B5, which takes it to I5. That is it. The OFFSET function returns value from cell I5 which 177.
Advantages of this lookup technique?
This is fast. The sole advantage of this method is that it is faster than the other methods. The same thing can be done using the INDEX-MATCH function, or VLOOKUP function. But it is good to know some alternatives. It may come handy some day.
So yeah guys, this is how you can use the OFFSET and MATCH function to LOOKUP values in Excel Tables. I hope it was explanatory enough. If you have any doubts regarding this article or any other excel/VBA related topic, ask me in the comments section below.
Related Articles:
Use INDEX and MATCH to Lookup Value: The INDEX-MATCH formula is used to lookup dynamically and precisely a value in given table. This is an alternative to the VLOOKUP function and it overcomes the shortcomings of the VLOOKUP function.
Sum by OFFSET groups in Rows and Columns: The OFFSET function can be used to sum group of cells dynamically. These groups can be anywhere in the sheet.
How to Retrieve Every Nth Value in a Range in Excel: Using the OFFSET function of Excel we can retrieve values from alternating rows or columns. This formula takes help of the ROW function to alternate through rows and COLUMN function to alternate in columns.
How to use OFFSET Function in Excel: The OFFSET function is a powerful Excel function that is underrated by many users. But experts know the power of the OFFSET function and how can we use this function to do some magical tasks in Excel formula. Here are the basics of the OFFSET function.
Popular Articles:
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
How to use Excel VLOOKUP Function| This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
How to use the Excel COUNTIF Function| Count values with conditions using this amazing function. You don't need to filter your data to count specific values. The COUNTIF function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
