IF/AND statement with index-matching

Question

So I had a very long nested-if statement, and the last nested-IF was an IF/AND statement with index matching. The formula worked fine (or so I thought), until a few weeks later when I needed to add another IF/AND statement with index-matching. Then the whole thing wouldn't even run because Excel thought I had too many arguments for the function.

So, this weekend I was putzing around with the formula, and I started parsing out bits and realized this IF/AND statement was only partially working.

Here's what I mean:

(my formula is in the Priority column (col C) of sheet 1)

If the STATUS(col. B) says Approved, I want my formula to index-match the priority from sheet 2, unless that cell is blank - then I want the priority on sheet 1 to say "Medium." If Status on sheet 1 doesn't say Approved, then I don't want my formula to do anything in that cell.

=IF((AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="")),"Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)))

which goes in in col. C of sheet1:

Here is sheet 2:

Item 10 works well - Status is Approved and priority on sheet 2 is blank so col. C says "Medium."

But, col C for items 20 and 25 have been index-matched when they shouldn't - they're status is not Approved, so they should be left blank.

I had a couple of ideas about remedying this, but neither worked. One idea was to add an AND(B2="Approved") to the last index-match,

=IF((AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="")),"Medium", (AND(B2="Approved",INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)))))

Which just returns a bunch of TRUE or FALSE.

I also tinkered with adding another IF into that last index-match bit, but I realized that it wouldn't be one cohesive IF statement anymore.

How can I change my formula to get it to work? Sorry if the answer is obvious, I'm pretty new to Excel.

Solution

Test if "Approved" first. If false return "" if true then do the if for the index:

=IF(B2="Approved",IF(INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0))="","Medium", INDEX(Sheet2!B:B,MATCH(Sheet1!A2,Sheet2!A:A,0)),"")