Excel Dynamic Lookup Formulas

< Back to Search results

 Category: [Excel]  Demo Available 

Excel Dynamic Lookup Formulas


Locating the Correct Excel Column

Dynamic Lookups in Excel You can download a working sample from the here

These are very handy for when you lookup data but cannot be sure which column your returned data should come from. In other words, users may have inserted a column within the table. The VLOOKUP is about the most popular of the Lookup functions so we will use this function for examples. 

Ok, let's go with a sample using a small table of data for ease of understanding. You can download a working sample from the here

The layout of our data are headings in row 1 from A1:D1 on a Worksheet called "Data".
Names | Department | Age | Gender | Pay Rate P/H

Directly underneath these headings is related data. For simplicity though, only down to row 8.

*You can, if you wish, use many more rows than A1:A19,B1:B19 etc to ensure your dynamic named ranges will keep expanding*

1) While on the "Data" Worksheet Go to Insert>Name>Define.

2) In the "Names in Workbook:" box type: Data_Table.

3) In the "Refers to:" box type, or copy/paste;

4) Click Add but not OK, just yet.

5) Repeat steps 2, 3 and 4 using the names (no quotes) and formulas below.






6) Now after clicking Add for the last named range, click OK.

It is vital that there is no other data on the "Data" Worksheet other than your table of data occupying A1:E8.

Let's now show some formulas that can be used to lookup a name in the table (Data_Table) and return the "Department" they work in.

=VLOOKUP("June K",Data_Table,COLUMN(Department),FALSE)

This formula will locate the name "June K" in the 1st column of data (Names) and return the corresponding row from the "Department" column. Should a user insert a column anywhere within, or at the end of the table, the formula will still return the correct result. However, should a column be added before Column A (making "Names" become column B) it will fail. To address this potential problem we could use Index with Match.

=INDEX(Data_Table,MATCH("June K",Names,0),MATCH("Department",Headings,0))

This formula is the most flexible as not only will it accommodate columns being added anywhere within, before or after the table, we can also use it to look left of our Names column. This may be needed when/if a column is added to the Start of our table. 

=VLOOKUP("June K",Data_Table,MATCH("Department",Headings,0))

Again though, as it uses VLOOKUP it will always look in the first column only of the table (Data_Table) for the name.

=June_K Department

This last one requires us to add some more named ranges, but we can do so with the aid of Excel.

1) Select the table of data but do not include any headings.

2) Go to Insert>Name>Create and ensure that ONLY "Left column" is checked and click OK.

Ok, we have now created a named range for all our names. It should be noted that, as spaces are not allowed, Excel would replace any spaces with the underscore. That is "June K" would be named "June_K".

Now we can simply Enter the Intersection formula as shown below into any cell and it will return the Department (or any other column) of the persons name we use. In this case it's "June K". It is vital to note that there IS a space between "June_K" and "Department"

=June_K Department


See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets


See also:

Excel Formulas/Formulae, Tips, Tricks and Feature Examples
Excel Charts : Delete a Single Entry From Excel Chart Legend
Dependent Validation Lists
Different Width Chart Bars. Stacked Fat/Thin Bar Chart
Excel Dynamic Formulas. Flexible & Changeable Formula Arguments


Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.


Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft.


stars (0 Reviews)