complex lookup Archives - Xelplus - Leila Gharani

How to Get Multiple Match Results in Excel Without the FILTER Function

Leila Gharani — Thu, 28 May 2020 09:00:00 +0000

If you don't have the Filter function in Excel, you need to check out this easy formula approach to get multiple match results in Excel. Download the workbook and use the version that fits your problem best.

The post How to Get Multiple Match Results in Excel Without the FILTER Function appeared first on Xelplus - Leila Gharani.

Excel DGET Function Solves Two of Your VLOOKUP Problems

Leila Gharani — Thu, 16 Jan 2020 10:00:00 +0000

Quickly learn how to use Excel's DGET Function to lookup one or more criteria. DGET can also do a left lookup (unlike VLOOKUP). It's a forgotten Excel database function. Can you apply DGET to a range of cells? Find out in this article.

The post Excel DGET Function Solves Two of Your VLOOKUP Problems appeared first on Xelplus - Leila Gharani.

Return Multiple Match Values in Excel

Leila Gharani — Thu, 30 Aug 2018 10:26:00 +0000

What to do when VLOOKUP falls short? It can't return multiple match results. But there are formulas that can. Here, I show you 2 different methods that return multiple matches: a combination of INDEX & AGGREGATE, which are available in all Excel versions, and TEXTJOIN, which was first introduced in Excel 2016 and Office 365.

The post Return Multiple Match Values in Excel appeared first on Xelplus - Leila Gharani.

Excel Complex Lookup: Find Header

Leila Gharani — Tue, 05 Sep 2017 09:52:00 +0000

In this tutorial we tackle a complex lookup problem - how to return the header based on criteria in a matrix. I offer my solution using INDEX and SUMPRODUCT. We also cover alternative approaches proposed by the Excel community, including an interesting TEXTJOIN solution that is able to handle multiple match results.

The post Excel Complex Lookup: Find Header 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.

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.