One method to extract data with helper column in excel worksheet is by using INDEX & MATCH functions. Helper column marks the matching values. This helps to prevent the complication of a complex formula arrangement.
Formula
The generic formula to extract data with helper column is given below
=IF(rowcheck,INDEX(Data,MATCH(rownum,helper,0),column),"")
Explanation
Managing duplicates is a problem while using extraction formulas. Lookup formulas, for instance, INDEX & MATCH and VLOOKUP can effortlessly get the first matching value, but it’s more difficult to find all the matching values when different matches is selected by the criteria. To solve this problem this formula uses helper column which yields a numerical value. This numeric value helps to extract data with helper column easily and more efficiently.
Example
In this example, we have used INDEX and MATCH functions to extract data with helper column (see screenshot). The formula of the helper column in the given example is
=SUM(E2,AND(C3=$I$3,D3=$J$3))
The helper column checks each row to examine that the Department (column C) is similar to that of I3 while the Building (column D) is the same as that of J3. The result of these two logical tests always gives TRUE results so that AND gives a TRUE result.
Figure 1 Example of Extract Data with Helper Column in Excel
The AND result, for each row, gives a count when inserted in the “value” of the helper column. This value changes only if a new matching value is found. It is useful because TRUE results which are returned due to AND are pushed to 1 while FALSE results are pushed to 0. 1 is added in case of TRUE results while nothing adds in FALSE results.
For “Name”, the formula (column H) is following;
=IF($G6<=ct,INDEX(data,MATCH($G6,helper,0),1),"")
Whereas;
- INDEX= All 3 data columns i.e. Name, Department, Building.
- MATCH= To match row number in the helper column
- IF= Statement wrapping the INDEX/MATCH formula
- ct= Maximum value in the helper column
Leave a Comment