In this Excel Video Lesson I demonstrate a great tip that dramatically simplifies data lookups!
Yes, learning how to use the Lookup Functions (VLOOKUP, HLOOKUP, MATCH, INDEX) is important. But, there is an easier way to answer some simple questions about you data.
In this video, I show you how to use the combination of “Named Ranges” and the “Intersection Operator” – the SPACE – to return the value at the intersection of a specific column and specific row. This “tip” is guaranteed to save you time and simplify your data lookups!
Here are the steps to follow in this lesson:
- Create “Named Ranges” for your data set.
- Use the “Intersection Operator” – the SPACE – in your formula. e.g. =Region_2 Week_16
- It’s that simple!
NEW! Download the Excel file that I used in this video lesson:
Looking for the answer to your Excel Questions? Use my Index of Excel Topics to find the right video
News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.
Related Video Lessons
Link to this post!
Clicking the link (which
looks as though it’s for downloading the Excel file):
http://www.thecompanyrocks.com/wp-content/uploads/2009/03/simplify-data-lookups.htm
on the page:
http://www.thecompanyrocks.com/simplify-data-lookups-in-excel/
brings up this error:
To use this Web page interactively, you must have Microsoft® Internet Explorer 5.01 Service Pack 2 (SP2) or later and the Microsoft Office 2003 Web Components.
See the Microsoft Office Web site for more information.
That seems like an unusual requirement for downloading an Excel file.
John
Hi John –
I will not have a chance to look into this for a few days. However, I do believe that the Office 2003 Web Cmponents are required.
What I will probably do, is revise these files for downloading as “static” HTML files. Excel 2007 does not offer the “Publish as Webpage” feature that I used for these in Excel 2003.
Thanks for alerting me to this.
Danny Rocks