fbpx
Search
Close this search box.

Product Recommendation – Excel Lookup Toolbox

Share

Facebook
Twitter
LinkedIn

Excel Lookup Toolbox - Product ReviewAnyone working on the data using excel will know the importance of lookup formulas. They are vital for making almost any spreadsheet or dashboard. That is why when my friend John Franco, who maintains Excel-Spreadsheet-Authors.com, wrote to me about his new book Excel lookup toolbox I was truly excited.

What is in this book?

The Excel lookup toolbox has 120 pages of very useful information on writing lookup formulas (VLOOKUP, INDEX, MATCH) and associated excel formulas like SUMPRODUCT, COUNTIF, SUMIF, Array Formulas etc.

The book starts with a detailed overview of the formulas vlookup, index, match, countif, countifs, sumif, sumifs, sumproduct, averageif, averageifs and array formulas.

Then the book moves to describe 8 familiar lookup scenarios,

  1. Looking up a number/date/text equal to
  2. Looking up text that begins with/ ends with / contains
  3. Looking up case-sensitive text
  4. Excel Lookup Toolbox - Example Page - #65Looking up two index columns and other advanced lookup formulas
  5. Looking up the max/min number, newest/oldest date etc.
  6. Looking up the exact or lower approximate text/number/date
  7. Looking up the exact or lower/greater approximate text/number/date
  8. Looking up the amount of a category

For each of these lookup scenarios, John explains how to write lookup formulas, gives a handful of practical examples (with illustrations) and highlights common mistakes and errors.

Read a sample page of the book.

Benefits of using Excel lookup toolbox

Anyone reading this blog for more than a week would appreciate the important role excel formulas play in building better excel worksheets / models / reports / dashboards. I think there are several benefits of really learning excel lookup formulas.

In my opinion, this book will help you,

  • become better at understanding the structure of your data(base)
  • write smarter and accurate lookup formulas, thus saving time and effort
  • not rush to Google or ask in forums on how to fetch the “x” from “y”
  • know exactly why an error is occurring when you see a #N/A or something equally annoying.
  • learn how to fix errors in lookup formulas and better still, know how to avoid such errors

What you get when you buy the toolbox?

When you purchase the Excel lookup toolbox you get,

  • A 120 page e-book on Excel Lookup Formulas (PDF)
  • 45 Excel files with detailed examples of lookup formulas
  • A fact-sheet detailing various errors that occur when writing lookup formulas and the remedies

How Much is the Excel Lookup Toolbox?

For a limited time John is selling the toolbox for $19.85. Believe me when I say this toolbox is a steal.

Go here to purchase.

My Review & Opinion

John uses his prior experience in system development to give us several real world scenarios and how the lookup formulas can be written for these. I really liked the way the John presented the content. See the sample page above.

I recommend the excel lookup toolbox for anyone working on excel to manage and report data. The book can teach you some really awesome tricks and help you become better at writing lookup formulas. That, my dear reader is the easiest way to impress everyone at work and get things done.

Any questions?

Do you have any questions about this product? Please drop a comment, John has agreed to reply to your queries directly here. If you bought the product, please feel free share your review here because it will help others.

Disclosure

John is a friend of mine. I have reviewed this ebook even before release (you can read my testimonial in the book as well). I get small commission when you buy the excel lookup toolbox. But I am 100% sure you get much more value out of the toolbox than what John or I make from the sale. Go ahead and get the excel lookup toolbox today.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

letter grades from test scores in Excel

How to convert test scores to letter grades in Excel?

We can use Excel’s LOOKUP function to quickly convert exam or test scores to letter grades like A+ or F. In this article, let me explain the process and necessary formulas. I will also share a technique to calculate letter grades from test scores using percentiles.

4 Responses to “Product Recommendation – Excel Lookup Toolbox”

  1. Art Johnson says:

    Does the book deal with the OFFSET function? I've got a couple of dynamic spreadsheets and I use a formula with OFFSET and COUNTIF or COUNTA.

    However, since Excel can make a range dynamic using List(2003) or Table(2007), I'm wondering why would I need this type of forumula instead of a standard Lookup formula?

  2. John Franco says:

    Hi Art,

    Thanks for your question!

    The function OFFSET(reference,rows,cols,height,width)
    returns a reference (A2, C6, etc) or an array (A2:A5, B3:B9, etc) that you can use in any formula that requires such argument.

    For example: you can feed the formula =SUM() this way =SUM(A2:A6) or this way =SUM(OFFSET(A1,1,0,5,1)) with the same results.

    Of course, you will use the OFFSET function with some condition that changes. Generally speaking, this function only feeds other formulas.

    Well...

    In brief, you need the lookup formulas to do the search and summary job, and eventually, if you have dynamic spreadsheets use the OFFSET function to specify arrays and cell references dynamically.

    For example, this array formula from the "Excel Lookup Toolbox" ebook searches two index columns (order number and quantity) and returns the sum based on the specified column...
    {=INDEX(Haystack!$A$2:$C$5,MATCH(1,(Haystack!$A$2:$A$5=A2)*(Haystack!$B$2:$B$5<=B2),0),3)}

    and it is the same that this other one using the OFFSET function as the lookup_array for the MATCH function
    {=INDEX(Haystack!$A$2:$C$5,MATCH(1,(OFFSET(Haystack!A1,1,0,4,1)=A2)*(Haystack!$B$2:$B$5<=B2),0),3)}

    Conclusion:

    The book provides the lookup building blocks (45 lookup scenarios); you can feed any of these formulas with your dynamic range (OFFSET)

  3. John Franco says:

    Art,

    The most explicit answer to your inquiry is NO, the book does not include the OFFSET function.

    I hope it helps you to decide and order!

  4. prakash b bajaj says:

    Dear Mr John

    I would like to buy your book
    for lookup table
    please send me detail about where to make payment

    Regards

    Prakash B Bajaj

Leave a Reply