If you have multiple excel tables and you want to do a dynamic VLOOKUP Function from these tables, you will need to use the INDIRECT function. In this article we will learn how easily you can change your lookup table by just changing the name of the lookup table in a cell.
Generic Formula for Dynamic Table VLOOKUP
| =VLOOKUP(lookup_value, INDIRECT("TableName"), col_index,0) |
Lookup_value: The value you are looking for.
TableName: The table in which you want to look for the lookup value.
Col_Index: The column number from which you want to retrieve data.
Let's hope for an example to see how it works.
assigned in Southern cities. The second table is named West and contains the details of the same employees when assigned in cities of the West.
Now we need to get the cities of employees at the same place from both regions.
As you can see in the image, we have prepared a table down. It contains the ids of employees. We need to get the cities from South and West using a single formula.
Apply the above generic formula to write this formula for dynamic VLOOKUP:
| =VLOOKUP($A24,INDIRECT(B$23),3,0) |
We have locked the references using $ sign so that when we copy the formula it takes the right references.
| If you aren't familiar with reference locking or absolution, you can learn it here. It is really important if you want to master Excel. |
Write the above formula in cell B24, copy in the whole range.
You can see that it has looked up the city of South from the South table and City of West from the West table, dynamically. We did not need to change anything in the formula.
How does it work?
It is a basic VLOOKUP formula with only difference of INDIRECT function. As you know, the INDIRECT function converts any text reference into actual reference, we use the same ability of INDIRECT function here.
It is important that you use an Excel Table or name your tables using named ranges.
In B24, we have formula VLOOKUP($A24,INDIRECT(B$23),3,0). This resolves to VLOOKUP($A24,INDIRECT("South"),3,0). Now indirect converts "South" into actual table reference (we have named the first table as South. Hence the formula is now VLOOKUP($A24,South,3,0). Now VLOOKUP simply looks up at the SOUTH table.
When we copy formulas in C24, the formula becomes VLOOKUP($A24,INDIRECT(C$23),3,0). C23 currently contains "West". Hence, the formula will ultimately resolve to VLOOKUP($A24,West,3,0). VLOOKUP gets value from the West table this time.
So yeah guys, this is how you can VLOOKUP dynamically using the VLOOKUP-INDIRECT combo. I hope I was explanatory enough and it helped you. If you have any doubts regarding this topic or any other excel VBA related topic, ask me in the comments section below. Till then keep learning and keep Excelling.
Related Articles:
Use INDEX and MATCH to Lookup Value: The INDEX-MATCH formula is used to look up dynamically and precisely a value in a given table. This is an alternative to the VLOOKUP function and it overcomes the shortcomings of the VLOOKUP function.
Use VLOOKUP from Two or More Lookup Tables | To lookup from multiple tables we can take an IFERROR approach. Looking up from multiple tables takes the error as a switch for the next table. Another method can be an If approach.
How to do Case Sensitive Lookup in Excel | the excel's VLOOKUP function isn’t case sensitive and it will return the first matched value from the list. INDEX-MATCH is no exception but it can be modified to make it case sensitive. Let’s see how…
Lookup Frequently Appearing Text with Criteria in Excel | The lookup most frequently appears in text in a range we use the INDEX-MATCH with MODE function. Here's the method.
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. 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.
