Help with if and vlookup

so i can't go into too much detail about why/what I'm working on, but here's an example spreadsheet.

1. I want to be able to type the formula into cell D2,
2. have it recognize the value in A2 as a non-match
3. then using the value in C2, find C11
4. verify that A11 says "seems valid"
5. look up D11, and return the value in D11 to D2

i'm not sure that having recognizing A2 as a non-match is necessary since I can sort by non-match and manually input the formula into the D column for each non-match row..

please let me know if that makes sense.. thanks!