Index match or Vlookup multiple columns

Hi All,

I have here a file which i need your help.

On Column A to E are the data extracted.
Column H to K is the given data that i should look up the value in column A to E.

If the customer in column H has a deletion mark X in column B then tha output should display = DELETED
If the customer in Column H is not found in extract then = DONT EXIST
if the customer dont have del;etion mark = ACTIVE

Note that there are duplicated customers. And the look up should be based on Customer Number, Country, DV and DC Column.
Sample below:

Capture.JPG

Attached is the file.Hope you guys can help.

Hi Ben,

Wow that worked like a charm!!

how about this formula? How can i add a message since it was returning 0 if the lookup is blank? This is related also for above, im thinking for anopther way around.

=INDEX($B$1:$B$153,MATCH(1,(J52=$A$1:$A$153)*(M52=$F$1:$F$153),0))

Also, in this part of the given formula:

=(COUNTIFS(A:A,H3,C:C,I3,D:D,J3,E:E,K3),"ACTIVE","DON'T EXIST"))

I noticed that Column B is not included. So i tried to altyer the formula to:

=(COUNTIFS(A:A,H3,C:C,I3,D:D,J3,E:E,K3,B:B,""),"ACTIVE","DON'T EXIST"))

And i got the same result. Is if fine?

Thanks Glen~! Tried that also and works very well!

Just want to ask how can i add "ACTIVE" if the result is 0?

=INDEX($B$1:$B$153,MATCH(1,(J52=$A$1:$A$153)*(M52=$F$1:$F$153),0))