If you want to find out where a value sits in a list, which row or which column it lands on, the MATCH function is what you’re looking for. It hands you back the position as a number, not the value itself. That number feels useless on its own, but it’s the piece that powers …
Excel has a lot of functions – about 450+ of them. And many of these are simply awesome. The amount of work you can get done with a few formulas still surprises me (even after having used Excel for 10+ years). And among all these amazing functions, the INDEX MATCH functions combo stands out. I …
If you want to break a piece of delimited text into separate pieces, like splitting a code at each pipe or a list at each comma, the TEXTSPLIT function is what you’re looking for. You give it the text and the character to split on, and it does the rest. TEXTSPLIT is a dynamic array …
If you want a clean list of distinct values from a column full of repeats, the UNIQUE function is what you’re looking for. You point it at a range and it hands back one of each value. You don’t need the Remove Duplicates dialog, and your original data stays untouched. UNIQUE is a dynamic array …
If you want to fit a straight line to your data and get the slope, the intercept, and how good the fit actually is, the LINEST function is what you’re after. It runs a least-squares regression and hands back the numbers behind the line. LINEST returns a whole array of numbers, not just one. In …
If you want to find a value in a sorted row or column and pull back the matching item next to it, the LOOKUP function does exactly that. It hunts for your value, and when there’s no exact hit, it settles on the largest value that isn’t bigger than what you asked for. In Excel …
If you want to look up a value using two or more conditions at once, a regular lookup won’t get you there. It can only match on a single column, so the moment two rows share the same name (or the same month), it just grabs the first one it finds. The fix is to …
The IF function is one of the most useful functions in Excel. You give it a condition to check, and it gives you one result when that condition is met and a different result when it isn’t. Simple idea, and you end up using it everywhere. But a single IF can only check one condition …
If you want to find the largest value in a list in Power Query, the List.Max function is the quickest way to do it. It takes a list of values and hands you back the single biggest one. In this article, I’ll walk you through List.Max with practical examples, from finding the highest number and …
©trumpexcel.com – Free Online Excel Training
