Forum Discussion

ECJ53's avatar
ECJ53
Copper Contributor
Oct 21, 2020

Ability to return a sub-list from a master list?

I am working with a master list that has about 400 rows of data.  The first column is an 8 digit number. The first 5 digits of the number define a product line.  The last 3 digits identify a unique ...
excel
Formulas and Functions
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Oct 21, 2020

ECJ53 

 

You need an Array (CSE) formula, fixes the issue, works on PARTIAL MATCH mechanism.

 

  • This formula works with all versions of Excel.
{=IFERROR(INDEX(Sheet1!$A$2:$C$13, SMALL(IF(ISNUMBER(FIND($A$2, Sheet1!$A$2:$A$13)), MATCH(ROW(Sheet1!$A$2:$C$13), ROW(Sheet1!$A$2:$C$13)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)), "")}

 

  • Enter this formula in sheet2 cell C2, finish with Ctrl+Shift+Enter and fill across.
  • As soon you update cell A2 with new value, formula works accordingly.
  • Check the attached sheet.

 

Resources