You have your super XLOOKUP formula but it’s returning the #N/A error message.
Why?
And how do you fix it?
In this article, we will explore different reasons you’re seeing the #N/A error with XLOOKUP and the appropriate fix. We will then explore other errors you may encounter with XLOOKUP.
Download the sample file provided with this tutorial.
In this blog post, we create a Conditional Formatting rule to automatically highlight rows in an Excel list at each value change.
I’ve got multiple guests from the same company attending an event and I want each company section to stand out. The default banded rows of Excel Tables does not help with reading my attendee tracker.
For this example, I want to highlight groups of rows – each company in my list.
Let’s create a Conditional Formatting rule to automatically highlight each company section. The rule will format the entire row based on the company name changing from the previous row.
This is much easier to read compared to the classic banded row formatting used by Excel tables.
So, how can we do this?
[Read more…] about Highlight Rows when Value Changes – Excel Conditional FormattingIf you’ve ever extracted from messy text strings, you’ve probably spent far too much time wrestling with LEFT, MID, RIGHT, and FIND formulas.
TEXTSPLIT changes all of that.
Instead of performing formula gymnastics with the likes of MID and FIND, TEXTSPLIT can instantly convert a text string into a dynamic array that Excel can work with. Due to this, TEXTSPLIT has become one of the most useful text functions in modern Excel.
In this article, we’ll explore practical examples that show how TEXTSPLIT can simplify text manipulation and unlock more powerful dynamic array solutions.
[Read more…] about TEXTSPLIT in Excel: 9 Practical Examples You Can Use TodayIn Excel, it is possible to create a quick and effective visual that offers more flexibility than a traditional chart. With a clever combination of symbols, the REPT function, and a little formatting magic, you can build compact in-cell bar charts that are dynamic and informative.
In this article, we will create a bar chart inside a cell, colour it based on custom performance thresholds and add values as data labels.
Let’s create the following dynamic bar chart without relying on standard chart tools.
The Excel SWITCH function is a neat alternative to nested IF functions or the IFS function.
It is a useful substitute and a real timesaver compared to complex nested IF formulas. It is more compact and easier to read.
The SWITCH function compares a value (known as the expression) against a list of values, and returns the result for the first matching value. If a match is not found, a default result can be returned.
=SWITCH(expression, value1, result1, [default_or_value2, result2])
Expression: is the value to be tested against the list of values. This is typically a reference to a cell value such as A2.
Value1: is the first value (SWITCH can evaluate up to 126 values) to be compared against the expression.
Result1: is the action to perform if its corresponding value is a match to the expression.
Default: This optional default value provides SWITCH with an else argument. This is the result to return if there is no match found.
Let’s see some SWITCH function examples.
[Read more…] about Excel SWITCH Function – Nested IF Function Alternative
