Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

Lookup Any Table Range With 1 Lookup Function

| | Information Helpful? Why Not Donate.

Multi-Table Lookup

Current Special! Complete Excel Excel Training Course for Excel 97 - Excel 2003, only ~~$145.00~~. $59.95 Instant Buy/Download, 30 Day Money Back Guarantee & Free Excel Help for LIFE!

Got any Excel Questions? Free Excel Help

DEPENDENT LISTS WITH LOOKUP FUNCTIONS

We are going to look at how to use just ONE Vlookup Function to look in any number of named tables/ranges. To make it user friendly we are going to use a Data Validation trick with lists and have the 2 (or more) lists linked. That is, choosing an item from our 1st list will result in a corresponding list showing the second. To see how to do this, see Dependent Data Valiation Lists and even download the basic example.

1 VLOOKUP FOR MANY TABLES

Download Example

The principle we are going to use is the same as we used to link the 2 validation lists in the basic download example. That is, we use the Indirect Function which will allow Excel to see the content of any cell as either a range address, or, as in our case, a named range.

Our end Vlookup Function, based on the download example above, will be this;
=VLOOKUP($B$2,INDIRECT(SUBSTITUTE($A$2&2," ","_")),$C$2,FALSE)

$B$2 (Lookup_value) is the value we are going to be looking for.

INDIRECT(SUBSTITUTE($A$2&2," ","_")) (Table_array) is the named table we are going to look for $B$2 in and in the left most column of that table. Note the use of the SUBSTITUTE Function which substitutes any spaces with the underscore. This is because named ranges can never have spaces in their names. Also note the use of $A$2&2 and not simply $A$2! This is because we have already named the lists we use in the Data Validation list the same as whatever is chosen from A2. So, when we name our tables, we use the same name as their lists, but add a 2 on the end. For example, our table for Cities has been named "Cities2".

$C$2 (Col_index_num) contains a Data Validation list with the numbers 2 and 3 (only 3 columns in example). Our Vlookup will offset (right) that many columns from the left most column in the named table it looks in.

FALSE (Range_lookup) tells Vlookup we want an exact match (not case sensitive).

#STOP THE N/A!

As you change items in the Data Validation lists you will get #N/A! until a existing item or the correct named table is chosen. This can be rectified in a number of way, some less efficient that others though. See Stop #N/A! in Lookups

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

Microsoft Excel Training- From Beginner to Expert in 6 Hours/ EXCEL DASHBOARD REPORTS

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