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
Names | Department | Age | Gender | Pay Rate P/H
Directly underneath these headings is related data. For simplicity though, only down to row 8.
CREATE NAMED DYNAMIC RANGES Or here for all types of Named Ranges .
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;
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 WITH COLUMN()
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
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
Again though, as it uses VLOOKUP it will always look in the first column only of the table (Data_Table) for the name.
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"
Back to Excel Formulas Tips tricks etc
Excel Dashboard Reports & Excel Dashboard Charts 50% Off Become an ExcelUser Affiliate & Earn Money
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Instant Download and Money Back Guarantee on Most Software
Excel Trader Package Technical Analysis in Excel With $139.00 of FREE software!
Microsoft ® and Microsoft Excel ® are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
Some of our more popular products are below...
Convert Excel Spreadsheets To Webpages | Trading In Excel | Construction Estimators | Finance Templates & Add-ins Bundle | Code-VBA | Smart-VBA | Print-VBA | Excel Data Manipulation & Analysis | Convert MS Office Applications To...... | Analyzer Excel | Downloader Excel | MSSQL Migration Toolkit | Monte Carlo Add-in | Excel Costing Templates