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.

NAMING THE NEEDED RANGES
*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;
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A$1:$A$19),COUNTA(Data!$1:$1))

4) Click Add but not OK, just yet.

5) Repeat steps 2, 3 and 4 using the names (no quotes) and formulas below.
"Names"
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A$1:$A$19),1)

"Department"
=OFFSET(Data!$B$1,0,0,COUNTA(Data!$B$1:$B$19),1)

"Age"
=OFFSET(Data!$C$1,0,0,COUNTA(Data!$C$1:$C$19),1)

"Gender"
=OFFSET(Data!$D$1,0,0,COUNTA(Data!$D$1:$D$19),1)

"Pay_Rate_PH"
=OFFSET(Data!$E$1,0,0,COUNTA(Data!$E$1:$E$19),1)

"Headings"
=OFFSET(Data!$A$1,0,0,1,COUNTA(Data!$1:$1))

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 WITH COLUMN()
=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 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 WITH MATCH
=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.

INTERSECTION
=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

Gallery

stars (0 Reviews)

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

Website designed by Astrava Solutions Ltd.