Extract all the matches with helper Column

With Excel’s powerful functions IF, INDEX, and MATCH, we can find exactly what you’re looking for with a few clicks of the mouse. This step-by-step tutorial will show how easy it is to extract data using these tools and more!

Generic Formula


=IF($F5<=$F$2,INDEX($A$2:$D$12,MATCH($F5,$D$2:$D$12,0),1),””)

Summary


Excel’s INDEX and MATCH functions can be used to extract multiple matches in a single cell. The need for an array formula is unnecessary when simple indexing formulas are available, making this even easier like G5 shown here:

=IF($F5<=$F$2,INDEX($A$2:$D$12,MATCH($F5,$D$2:$D$12,0),1),"")

In this formula, F5 is the total number, A2:D12 is the total data, and D2:D12 is the helper column.

Clarification


Managing duplicate is a challenge with lookup formulas that can easily find the first match, but it becomes more difficult when you’re looking for “all matches.” Lookup functions like VLOOKUP and INDEX + MATCH will return only one instance of your criteria; however, if many items are meeting those requirements, then this process gets exponentially slower as each additional record needs to be processed individually instead of just using their index numbers or names–which would have been much simpler.

With this formula, we can easily extract multiple matches. It’s a lot easier than trying to match each individual prompt with arguments from previous rows! The helper column contains an equation like so:

=SUM(D1,AND(B2=$G$2,C2=$H$2))

how to extract all matches with helper column1

This helper column tests each row in the data to see if Department in column B and C values match up with their corresponding G2 and H2 value. The two logical operations must return TRUE for AND operation, so they both must return True!

The AND function has the practical effect of generating an incrementing counter that only changes when there is a match. This works because TRUE/FALSE values are coerced to 1 or 0 for addition in the sum operation, respectively; thus, they don’t contribute anything but their respective literals while adding up all other input characters together with spaces between them (for example, “the cat” would become just “+t”).

When we look at the extraction area, column G contains a formula that looks like this:

=IF($F5<=$F$2,INDEX($A$2:$D$12,MATCH($F5,$D$2:$D$12,0),1),"")

how to extract all matches with helper column1

INDEX + MATCH looks up the name for a match found, using row number in column F as its value.

INDEX($A$2:$D$12,MATCH($F5,$D$2:$D$12,0),1)

INDEX uses the array (($A$2:$D$12) and receives the data of all 3 columns, and MATCH is configured to match exactly what’s found in the helper column(the named one with the ‘helper’ title). The third argument tells INDEX that you want 3 fields matched: row number, name/value pair & text content itself.

The formula is clever because it doesn’t matter what values come in at the start; MATCH will only match one of them. Of course, the “helper column” contains duplicates, but they’re irrelevant since all we care about are those first few rows that correspond with each entry on our data table!

Except for the column number, the formulas in columns G and H are the same as column G, which is increased by one.

The IF statement that wraps the INDEX/MATCH formula is a powerful tool for checking each row in an extraction area to see if it equals some condition. In this case, we want matching records, and so F2 gets checked against the total counts of all matches, which gives us our match count (totalNumber). If there are less than equal numbers found, logic will run; else, everything remains “EMPTY.”

The formula in F2 named “totalNumber” is easy to use.

=MAX(D2:D12)

how to extract all matches with helper column1

The MAX function is all you need because it provides a total match count based on the helper column.

Conclusion


If you need to find a value in the intersection of two columns, this article will be helpful for you. Use INDEX and MATCH with a helper column for your most efficient solution.

Related Functions


  • Excel INDEX function
    The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
  • Excel MATCH  function
    The Excel MATCH function search a value in an array and returns the position of that item.The MATCH function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the MATCH function is as below:= MATCH  (lookup_value, lookup_array, [match_type])….
  • Excel MAX function
    The Excel MAX function returns the largest numeric value from the numbers that you provided. Or returns the largest value in the array.= MAX(num1,[num2,…numn])…

 

Related Posts

Basic Array Formula With Examples
6 Array Formula Examples1

Are you searching for an article for getting the basic array formula with different examples for better understanding? Then congratulations because you have just landed on the right article. In this article, you would get to know the basic array ...

How to Find the Earliest and Latest Date in Excel
Find the Earliest and Latest Date 10

We have a range of dates and we want to look up the earliest and the latest date based on certain criteria like the earliest date for a showing movie, we can use MIN and MAX functions with IF function ...

How to Create Dynamic Drop Down List without Blank in Excel
create dynamic drop down list with blank5

This post will guide you how to create dynamic drop down list without blank cells in Microsoft Excel. In Excel, and you can use Data Validation feature to improve the efficiency of data entry in excel, and it also be ...

How to Calculate the Average Excluding the Smallest & Highest Numbers in Excel
Calculate the Average Excluding the Smallest & Highest 10

Calculating the average for a batch of data is frequently used in our daily life. But for some cases like statistic the average score in a competition, or price analysis, we often calculate the average excluding the smallest and highest ...

How to Get the Maximum or Minimum Absolute Value in Excel
Get the Maximum or Minimum Absolute Value 10

It is easy to find the maximum or minimum value in a batch of data in excel, but if this batch of numbers contains both positive and negative numbers, the maximum or minimum absolute value cannot be found out by ...

How to Find the Largest Value in a Column and Return the Adjacent Cell Value in Excel
find largest value return adjacent cell value2

This post will guide you how to find the largest value in a column and then return the adjacent cell value with a formula in Excel. How do I find the max value and return the cell value from the ...

How to Extract the Column Header of the Largest Value in a Row in Excel
extract column header of the largest value in row1

This post will guide you how to find the largest value in a row and return column header in Excel. How to retrieve the column header of the largest value in a row with a formula in Excel. How to ...

How to Find Max And Min Value with Single or Multiple Criteria in Excel
find max value based single criteria5

This post will guide you how to find the maximum value in a range of cells based on single or multiple criteria in Excel. How do I calculate the Minimum value based on criteria with MIN Function in Excel. Find ...

How to Check If value Is between Two Numbers in Excel
check value is between two numbers2

This post will guide you how to check if a cell value is between two numbers in Excel. How do I build an IF statement that test if a cell value is between two given values in formula in Excel. ...

How to Get Cell Address of Max or Min Value in a Range in Excel

This post will guide you how to return the cell address of a maximum cell value from a given range of cells with a formula in Excel. How do I Find the minimum value from a given range and return ...

Sidebar