INDEX/MATCH Archives - Xelplus - Leila Gharani

Conditional Format Index Match Result in Excel

Leila Gharani — Thu, 18 Jan 2018 07:44:00 +0000

Quickly learn how to conditionally format the result of your lookup value inside the original data set. If you conditional formatting formula is not working, it's probably because of incorrect referencing.

The post Conditional Format Index Match Result in Excel appeared first on Xelplus - Leila Gharani.

Excel Dynamic Chart with Drop-Down

Leila Gharani — Mon, 11 Dec 2017 07:36:00 +0000

With these simple steps you can easily create an interactive chart in Excel which will for sure impress your team. Once you learn the basics for dynamic chart ranges, you can create any dynamic graph you need in Excel.

The post Excel Dynamic Chart with Drop-Down appeared first on Xelplus - Leila Gharani.

Lookup Values Across Multiple Worksheets in Excel

Leila Gharani — Fri, 17 Nov 2017 07:40:00 +0000

In this tutorial, we will address how to lookup values in multiple tabs using a single formula. You will see one method using VLOOKUP and direct worksheet and cell references, and another using INDEX & MATCH and Excel Table names and references. Both approaches rely on the INDIRECT function to return the correct sheet address in a dynamic way.

The post Lookup Values Across Multiple Worksheets in Excel appeared first on Xelplus - Leila Gharani.

Dependent Drop-Down Lists in Excel with Multiple Words & Spaces in 1st List

Leila Gharani — Wed, 01 Nov 2017 14:32:00 +0000

See how to set up a dependent dropdown list. In this example, the first dropdown list is the column headers of a table, and depending on what the user selects, that will define the contents of the second dropdown.

The post Dependent Drop-Down Lists in Excel with Multiple Words & Spaces in 1st List appeared first on Xelplus - Leila Gharani.

Corporate Report Makeover: Alternative for Excel Doughnut Charts

Leila Gharani — Thu, 19 Oct 2017 14:18:00 +0000

Doughnut charts get a bad reputation, but they are still used in corporate reports. In this tutorial, we offer a better alternative. A bar chart with automatic sorting will tell the story in one glance. Learn, how to set it up.

The post Corporate Report Makeover: Alternative for Excel Doughnut Charts appeared first on Xelplus - Leila Gharani.

Excel Lookup Multiple Criteria in Rows or Columns

Leila Gharani — Thu, 17 Aug 2017 22:18:00 +0000

Learn how to perform a matrix lookup, i.e. when you need to return the value based on the header row and the category column, in a scenario where you have more than one header. The powerful INDEX/MATCH combo is capable of handling that a couple of ways.

The post Excel Lookup Multiple Criteria in Rows or Columns appeared first on Xelplus - Leila Gharani.

Excel Reverse Matrix Lookup

Leila Gharani — Fri, 21 Jul 2017 14:35:00 +0000

In this tutorial, we tackle a matrix lookup scenario, where you want to return the column header based on the value inside the matrix as well as the row header. INDEX/MATCH to the rescue.

The post Excel Reverse Matrix Lookup appeared first on Xelplus - Leila Gharani.

INDEX & MATCH for Flexible Lookups

Leila Gharani — Fri, 03 Mar 2017 12:45:00 +0000

Learn how the INDEX and MATCH functions work individually and how putting them together allows you to conduct flexible lookups.

The post INDEX & MATCH for Flexible Lookups appeared first on Xelplus - Leila Gharani.

Lookup Within Boundaries

Leila Gharani — Sun, 12 Feb 2017 10:20:00 +0000

This tutorial takes you through three powerful Excel formulas that lookup values in ranges that include lower bound and upper bound values. They are SUMIFS, SUMPRODUCT and INDEX/MATCH. Find the one that works best for your scenario.

The post Lookup Within Boundaries appeared first on Xelplus - Leila Gharani.

Excel Index Dynamic Range to find First Empty Cell

Leila Gharani — Sun, 05 Feb 2017 14:33:00 +0000

Learn how to create a description column that includes the number of sub-items within a main item and also provides the description of the main item. You can achieve that in Excel with a formula combining the COUNTA, INDEX and MATCH functions.

The post Excel Index Dynamic Range to find First Empty Cell appeared first on Xelplus - Leila Gharani.