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!
Table of Contents
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))
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),"")
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)
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])…