This is an INDEX/MATCH formula: =INDEX(setingsDBase,MATCH(D9,nfDrinksList,0),2) [See it used in the screenshot below]
It is a powerful time saving formula constructed using 2 different functions – enabling timely and convenient extraction of data from a database while reducing the chances of user entry errors being made during routine data recording of product sales record
Index/Match Formulas are indispensable tools for organizing data as well as creating reports and analyses. I have found them most useful for creating reports from spreadsheet databases in which the data to be handled is already summarized.
Indeed, I’ve found them especially useful in handling data summaries generated using SUMPRODUCT formulas . All my applications make generous use of these functions and most times they meet 80% of my needs. STUDY the formula using the notes provided on this page.
Next week I will create and send you a step-by-step video demonstration/explanation of how this Index/Match Formula was built
Below: Screenshot of MS Excel worksheet with explanatory notes on how the Index/Match formula I created was used to generate the report shown.
As usual, the workbook – with unprotected worksheets and VBA code modules was emailed to members of my Excel Heaven Visual Basic Automation Club. Click here to request a copy.
Posted on 23 October 2016
Tags: Analyzing and Presenting Data: Index/Match Formulas, Excel-Heaven Tutorial 04, Extracting, Organizing
About the Author
Tayo K. Solagbade is a Location Independent Performance Improvement Specialist and Multipreneur (i.e. a highly versatile/multi-skilled entrepreneur), with a bias for delivering Best Practice solutions to Farm Businesses and others.
Since 2002, he has earned multiple streams of income providing individuals and organisations with personal development training and coaching, custom MS Excel-VB solutions, web marketing systems, freelance writing services, and best practice extension support services (for farm business owners).
Tayo is the author of the Self-Development (SD) Bible™, the popular Livestock Feed Formulation Handbook, and developer of its accompanying Excel-VB driven Ration Formulator - as well as the increasingly popular Monthly Poultry Farm Manager app.
On 1st April 2013, Tayo (who reads, writes and speaks the French language) relocated to Cotonou, in the French Speaking Benin Republic on the first lap of his slow travels across the West African region. His key purpose is to deliver talks, seminars and workshops on his key areas of focus and interest to interested audiences (Email tayo at tksola dot com for details).
When he's not amazing clients with his superhuman skills (wink), Tayo works as the creative force behind his Daily Self-Development Nuggets blog - on which he also publishes previews of paid issues of The Farm CEO™ Newspaper (www.thefarmceo.net), in addition to his FREE Weekly Public Speaking/Web Marketing IDEAS newsletter - which he uses to promote Burt Dubin's Public Speaking Mentoring service to experts across the African continent.
Visit Tayo's Flagship Performance Improvement website to download over 10 performance improvement resources to boost your personal and work related productivity.
Join Tayo's international community of fans on his Flagship MS Excel Heaven Facebook page (click here). You can also connect with him via Twitter (@tksola).
Warning: count(): Parameter must be an array or an object that implements Countable in /home1/tayoswdg/public_html/sdnuggets/wp-includes/class-wp-comment-query.php on line 399