INDEX, MATCH, AND formula

EmilyH
EmilyH ✭✭✭✭✭
edited 12/09/19 in Smartsheet Basics

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.

Comments