< Go Back

Extract data with helper column

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
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar