Looking up values using MATCH, then return 1 of 3 different results

In "export.xls", column AI will sometimes have a value of "360", and sometimes not.
If it does, we are then interested column K of the same row, the "design_number".

In "360 DATABASE.xls", column A will have a list of "design_numbers" (with dashes removed).

Some design numbers from "export" will be found in "360 DATABASE", while some will not. This can be found easily enough using VLOOKUP.

My goal is to add a column in "export" next to column AI with the header "Die on-hand?". I want each row below this header to show 1 of 3 possibilities:
1. "360 die on-hand" if found in the "360 DATABASE".
2. "please add design" if the design_number is NOT found in "360 DATABASE", but column AI of "export" has a value of "360".
3. Nothing (blank), if the value "360" was not found in column AI of "export" for this row.

I have tried several very amateurish times to do this with simple IF and VLOOKUP functions, but I cannot get the result I am wanting. I need a macro that can perform this for me. The example files include 3-4 instances of possibility # 2 above, and many more of possiblities #1 and #3.

Many thanks to anyone who is willing to provide code. I not only want to use it, but to learn from it.

Your formula doesn't rely on the "360 DATABASE" being sorted in ascending order does it? I only ask because I want to know if I should include it in the macro.


Ah I see. The third argument in match is "0", so in this case, the array doesn't require sorting.

Thank you for your help.