INDEX, MATCH, AND formula

EmilyH

I am trying to create an Index / Match cross-sheet formula if two cross-sheet criteria are met.

Sheet One displays the Sales Reps for New and Modified Items:

I want to display the Item Rep for NEW item types.

The formula entered on Sheet Two is:

=IF(AND(MATCH(Item1, {SHEET ONE_ITEM}, 0), MATCH("NEW",

{SHEET ONE_ITEM STATUS}, 0)), INDEX({SHEET ONE_SALES

REP}, MATCH(Item1, {SHEET ONE_ITEM}, 0)))

It appeared to be working for Item One but when I copy and paste the formula, it gives different results. Also, if I move the Item Order on Sheet One, it displays different data. Same formula, I just moved the item order. I assume that my formula is only looking for the first Item ONE that matches NEW in the First Row on Sheet One.

Any ideas on how I can display Sheet One NEW item type Sales Rep on Sheet Two? A simple Index/Match formula will not work, I need two criteria to match before the data is indexed. The report should look like:

Thank you for your help.

EmilyH

Screen shots attached.

Paul Newcome

Instead of an INDEX/MATCH, try using a JOIN/COLLECT.

 

=JOIN(COLLECT({SHEET ONE_SALES REP}, {SHEET ONE_ITEM}, Item1, {SHEET ONE_ITEM STATUS}, "NEW"))

EmilyH

Hi Paul,

It worked, thank you so much for your help!

Emily

Paul Newcome

Happy to help!