3 Matches Plus an IF statement

Hello All,

See attached. In Sheet2, Column M, I am trying to input a formula that will return the value in the MOT MRS tab column F based on the following match:

Match 1
Sheet2 column G matches MOT MRS column G

Match 2
Sheet2 column E matches MOT MRS column E

Match 3
Once the above matches have been made, I need the formula to search for "BASEPACKAGE" in MOT MRS Column I. If it finds this for one of the corresponding projects, it should return the info in column F.

Caveat
If it makes all of the above matches but the word duplicate appears in MOT MRS column K, it should return the word “Duplicate”.

Does this make sense?

Life is too short to check a formula with over 10,000 rows to see if the formula works. I'm not going there. So I threw away all but 20 of them a) to get a result and b) to enable you to check that it's giving you what you expect.

Try this array formula:

Formula:

Please Login or Register  to view this content.

If it does what you need, then all you need to do is put your huge range back in again.