The function combination
OFFSET MATCH MATCH can be useful for looking up values in a two dimensional array of data. The primary benefit of using
OFFSET MATCH MATCH is that it is intuitive and that many people are familiar with the
In some ways, using the
OFFSET function to find a lookup value is more intuitive than using the
INDEX function because it is very similar to moving a piece around a chess board.
Additionally, some people are more familiar with the
OFFSET function than they are with
INDEX; in that situation, it may make sense to go with
OFFSET MATCH MATCH as your lookup formula.
Full article: How to use OFFSET MATCH MATCH
Many of you will be familiar with the issues that arise when non-experts develop applications and/or when applications are developed in non-typical ways. This leads to errors, poor process and inefficiency.
But that's not the big deal. The big deal is that:
- Those applications represent wasted effort.
- The technology department is out of touch with its user base.
- Users have been doing the easy thing and not the right thing.
- End user developed applications don't feature in planning and strategy but they are an impediment to execution of that strategy.
Full article: What's the BIG deal about end user development?
IFERROR formula was designed to solve a common aesthetic problem that most of us have encountered when using Excel – when we know that there are errors in our data, but we'd prefer not to see Excel's standard error message notation.
IFERROR has become very popular because it allows you to mask Excel's error message with a blank space, or a custom message of your own, such as "Not Found".
IFERROR becomes problematic when you encounter an error condition in your data set that you were not expecting. While
IFERROR is definitely efficient and also very intuitive, it's not always the best formula to use to mask errors because it can clearly cause problems down the line. This article discusses alternatives.
Full article: Why you should be cautious about Excel's IFERROR formula
Whether you're just starting out on your Excel journey or have been using it for a while, there are a few skills that you should know about Excel: the best ways of doing things, certain pitfalls to avoid, things that will impress other people (especially your boss).
In this article, I will talk about the 20 most important skills you need to know about Excel, including some of the best hints and tips I can think of to get you started as a beginner – or make you more productive as an experienced user.
Full article: Top 20 essential Excel skills you need to know
One of Excel's greatest cell reference features is a 3D reference, or dimensional reference as it is also known.
A 3D reference in Excel refers to the same cell or range of cells on multiple worksheets. It is a very convenient and fast way to calculate data across several worksheets with the same structure, and it may be a good alternative to the Excel Consolidate feature.
This blog covers:
- What is an Excel 3-D reference?
- Creating a 3D reference in Excel.
- Including a new sheet in an existing 3D formula.
- Creating a defined name for a 3D reference.
- List of functions supporting 3D references.
- How Excel 3D references change when you insert, move or delete sheets.
In Excel we can bin numbers. We can also bin letters. Rare but possible. However, be careful when binning a mix of letters and symbols! What could go wrong? A student who earns a B- could end up with an A- grade!
This happens because the
TRUE option of the
MATCH function doesn't work as you might expect.
Full article: The dangers of binning in Excel
Writing a nested
IF statement is widely considered a rite of passage for beginning Excel users who are looking to become more advanced.
The overall concept of writing a nested
IF is actually fairly simple; it's just that process of doing so is highly prone to making mistakes, which can make learning it somewhat difficult.
Having a strong level of comfort around logical structuring is critically important to Excel proficiency. Therefore, if you want to get good at Excel, you should learn how to write nested
IF statements regardless of which approach becomes your preference.
Full article: How to write a nested IF statement in Excel
This article provides a complete guide to the use of VBA dictionaries.
In VBA we can use Arrays and Collections to store groups of values. For example, we could use them to store a list of customer names, student marks or a list of values from a range of cells. A Dictionary is similar to a Collection.
The Collection, however, has two major faults: We cannot check if the key already exists, and we cannot change the value of an existing item. The VBA Dictionary does not have these issues. You can check if a Key exists and you can change the Item. This makes the Dictionary a very useful and versatile data structure.
Full article: Excel VBA dictionary – A complete guide
Flash Fill is like a copy cat, which can do the things in same pattern in which you are doing. You just have to do that operation once & Flash Fill do it for the rest. It can help you to make your data entry much easier & make you less dependent on complex formula for little things.
This article includes 10 examples of using Excel's Flash Fill feature, showing you how to use it to manage your data effectively.
Full article: How to use Flash Fill in Excel – 10 useful examples