• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

INDEX, MATCH WITH MULTIPLE CRITERIA

kkimiri

New Member
In the attached file, please assist on how I can index & match with both unit and project to give the result in cell B10. Currently I am only able to index & match with only one criteria (Unit) as per the formula in cell B10.
 

Attachments

  • Index & Match Multiple Criteria.xlsx
    22.8 KB · Views: 5
Would it be feasible to do this with a pivot table?
In the attached, select your Unit and Project in cells I1:I2 and your names will appear from cell H5 down.
For a formula: Which version of Excel do you use (will it handle worksheet functions such as Filter and Unique)?
 

Attachments

  • Chandoo46849Index & Match Multiple Criteria.xlsx
    26.9 KB · Views: 3
Would it be feasible to do this with a pivot table?
In the attached, select your Unit and Project in cells I1:I2 and your names will appear from cell H5 down.
For a formula: Which version of Excel do you use (will it handle worksheet functions such as Filter and Unique)?
Thank you very much. Appreciated. i would also like to use a formula for the same. I am using Microsoft Excel 2016. Kindly could you assist with the proposed formula?
 
See cell B10 in the attached.
If your version of Excel supports UNIQUE and FILTER there's a formula D10 as a cross check, as well as the pivot also being a cross-check.
 

Attachments

  • Chandoo46849Index & Match Multiple Criteria.xlsx
    27.5 KB · Views: 2
Since the OP's Excel version is Excel 2016, of which do not have UNIQUE and FILTER functions.

Here is the formula solution for Excel 2019 or below.

Changed your formulas in the following position :

1] Sheet "Index" H7, formula copied down :

=IFERROR(INDEX('Transaction listings'!F:F,AGGREGATE(15,6,ROW('Transaction listings'!F$2:F$18)/('Transaction listings'!C$2:C$18=E$5)/(COUNTIF(H$6:H6,'Transaction listings'!F$2:F$18)=0),1)),"")

2] Sheet "View" B10, formula copied down :

=IFERROR(INDEX('Transaction listings'!A:A,AGGREGATE(15,6,ROW('Transaction listings'!A$2:A$18)/('Transaction listings'!C$2:C$18=Index!E$5)/('Transaction listings'!F$2:F$18=Index!H$5)/(COUNTIF(B$9:B9,'Transaction listings'!A$2:A$18)=0),1)),"")


76107
 

Attachments

  • Index & Match Multiple Criteria (BY).xlsx
    27.1 KB · Views: 13
See cell B10 in the attached.
If your version of Excel supports UNIQUE and FILTER there's a formula D10 as a cross check, as well as the pivot also being a cross-check.
Thank you very much, i will try get an updated version of Excel to use the UNIQUE and FILTER functions. Much Appreciated.
 
Since the OP's Excel version is Excel 2016, of which do not have UNIQUE and FILTER functions.

Here is the formula solution for Excel 2019 or below.

Changed your formulas in the following position :

1] Sheet "Index" H7, formula copied down :

=IFERROR(INDEX('Transaction listings'!F:F,AGGREGATE(15,6,ROW('Transaction listings'!F$2:F$18)/('Transaction listings'!C$2:C$18=E$5)/(COUNTIF(H$6:H6,'Transaction listings'!F$2:F$18)=0),1)),"")

2] Sheet "View" B10, formula copied down :

=IFERROR(INDEX('Transaction listings'!A:A,AGGREGATE(15,6,ROW('Transaction listings'!A$2:A$18)/('Transaction listings'!C$2:C$18=Index!E$5)/('Transaction listings'!F$2:F$18=Index!H$5)/(COUNTIF(B$9:B9,'Transaction listings'!A$2:A$18)=0),1)),"")


View attachment 76107
Wooow, Great. Thank you sooo much!!! This works perfectly. Much Appreciated Excel Ninja. Thanks.
 
Back
Top