Although the VLOOKUP versus INDEX/MATCH debate gets most of the attention there are MANY more topics to debate in Excel. When calculating student grades what method is best: NESTED IF or LOOKUP/BINNING TABLE? Continue reading
Author Archives → Kevin Lehrbass
Video 00161 Extract Matrix Non Blanks Into 1 Column
Have you ever visited excelxor.com ? It’s an amazing blog with a lot of Excel formula challenges. Here’s one challenge: “given the the 6-by-4 array on the left we may wish to create the single-column on the right” Continue reading
Reverse Partial Match Lookup to Filter for a List
By default, Excel’s SEARCH function looks for one value inside of one cell. Jon Acampora from ExcelCampus (one of my favorite blogs) demonstrates how to look for various values within one cell with this formula:
=IFERROR(LOOKUP(1E+100,SEARCH(tblFilterList[Filter List],[@Name]),tblFilterList[Filter List]),””) Continue reading
Video 00160 Sum Values From Qualifying Mini Tables
Sometimes life is stranger than fiction. If we were to create this requirement as a hypothetical case many would say that it would never happen. Well, this was a real question that someone had for me. Read this post to see the requirements and how I solved this. I’ve also included alternative solutions from the Excel community. Continue reading
World Happiness Report
Recently I saw a link about the World Happiness Report. What is it? “…a landmark survey of the state of global happiness …which ranks 155 countries by their happiness levels” Can you guess what my first question was? Continue reading
Video 00159 Create unique and sorted text list
We have a bunch of names in a column. We need to create a unique and alphabetically sorted list. BE CAREFUL! Before we jump into a solution we should ask a couple of key questions. Does the solution need to be dynamic? Do we need to teach the solution to our co-worker or client? Continue reading
Video 00156 One Complex Formula or Helper Formulas?
There are many factors to consider when creating a solution in Excel. Are we building the solution for someone else with basic Excel skills? Should we use a complex formula in only one column to save space or use helper formulas to simplify the solution? Let’s take a look at this question from Mike Girvin’s ExcelIsFun YouTube channel. Continue reading
Unstacking Data with Oz du Soleil
In the movie Forrest Gump I’m sure you remember when Bubba lists different ways to prepare shrimp: “You can barbecue it, boil it, broil it, bake it, saute it. Dey’s uh, shrimp-kabobs, shrimp creole, shrimp gumbo. Pan fried, deep fried, stir-fried. There’s pineapple shrimp, lemon shrimp, coconut shrimp, pepper shrimp, shrimp soup, shrimp stew, shrimp salad, shrimp and potatoes, shrimp burger, shrimp sandwich. That- that’s about it.“ Continue reading
Video 00153 All Possible Pairs from List of Names
Oz du Soleil and I have been trading Excel ideas for a couple of years now. During ModelOff’s Global Training Camp in Toronto (Sept 22&23) I met Oz in person! Oz flew in from Oregon to deliver one of the Excel sessions. At one point Oz asked the following question: “Given a list of 10 names, how can Excel display all the name pairs?” Continue reading
Liam Bastick from sumproduct.com
I recently presented at ModelOff’s Global Training Camp in Toronto. One of the many great things about the training camp was that I had the pleasure of meeting Excel guru Liam Bastick. Continue reading