If-and-index-match

[jmegarner]

I am using the following formula and attempting to have APPROVED or BR entered into a cell.

=IF(AND(INDEX('Raw Data'!D:D, MATCH(Template!A2&"ATP-F",'Raw Data'!A:A&'Raw Data'!E:E, 0) =100%), (INDEX('Raw Data'!D:D, MATCH(Template!A2&"BR", 'Raw Data'!A:A&'Raw Data'!E:E,0) =100%))), "BR", "Approved")

I want the sheet to look at column A and E of the Raw Data tab and if a row has both the value of A2 and "ATP-F" (column E), I would like it then to check column D for % value and then I need the sheet to check the same columns but this time looking for "BR" in column E and if both return 100% value from Column D enter "BR" otherwise "Approved"

 

[Roderick_E]

maybe try control+shift+enter after put in formula? (as an array)

 

[jmegarner]

maybe try control+shift+enter after put in formula? (as an array)

I did use CSE the brackets did not copy over. When I attempt to evaluate the formula I am getting #VALUE error within the AND function.

 

[Marcelo Branco]

Wrong parentheses..

Try
=IF(AND(INDEX('Raw Data'!D$2:D$100, MATCH(Template!A2&"ATP-F",'Raw Data'!A$2:A$100&'Raw Data'!E$2:E$100, 0)) =100%,INDEX('Raw Data'!D$2:D$100, MATCH(Template!A2&"BR", 'Raw Data'!A$2:A$100&'Raw Data'!E$2:E$100,0)) =100%), "BR", "Approved")

Note: For the sake of performance avoid references to entire columns, like A:A, D:D or E:E, in array formulas. Use definite ranges like A$2:A$100 etc (adjust to suit)

M.

 

[jmegarner]

Thank you, Marcelo. It worked!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.