if(isnumber(match

[iqbalmel]

Hello, can you please help me with the formula in cell L1? It should read "OK" instead of "Review" since it meets the criteria in columns B and C. Thank you.

[Ace_XL]

How about..

=IF(COUNTIFS(B:B,H1,C:C,I1),"OK","REVIEW")

[Glenn Kennedy]

1. As written, it is an array fromula and needs to be set with CTRL-SHIFT-Enter.

2. It is NOt a good idea to use array formulae with whole column (e.g. B:B) references. You are forcing excel to look at 1,000,000 rows. Do you REALLY have that number of rows? Use a sensible range. A slight variant of your formula:

=IF(ISNUMBER(MATCH(1,INDEX(($B$1:$B$1000=H1)*($C$1:$C$1000=I1),0),0)),"OK","REVIEW")

will do the same job, faster, and does NOT need to be set as an array formula.

[iqbalmel]

Thanks Glenn! Appreciate the additional feedback as well - I changed the range.

[Glenn Kennedy]

You're welcome. But go with Ace XLs offering, if it works on your real sheet!

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.