Advanced Lookup Formula

I am in need of an advanced lookup formula that will search through data from one table and produce a value based on conditions from another table.

Table 1

Table 2

What I am trying to achieve is this:

1. If Column J in Table 1 is equal to column A in Table 2 and
2. Column L in Table 1 is equal to Column D in Table 2
3. But Column D in Table 2 is blank but there are values in Column L of Table 2 except the value specified in Table 2 previously
4. And Column G in Table 1 is equal to Column B in Table 2
5. But Column B in table 2 is blank but Column G in Table 1 has values except the one specified previously in Table 2
6. And if Column O in Table 1 is equal to Column C in Table 2
7. But Column C in Table 2 is Blank and Column O in Table 2 contains values other than previously specified in Table 2
8. The end result should be column E in Table 2.

I used this formula but it is showing a value error

=IF(AND(Trans!J:J=Mapping!A:A),Mapping!E:E),IF(AND(Trans!L:L=Mapping!D:D),Mapping!E:E),IF(AND(Mapping!D:D="",Trans!L:L<>""),Mapping!E:E), IF(AND(Trans!G:G=Mapping!B:B),Mapping!E:E),IF(AND(Mapping!B:B="",Trans!G:G<>""),Mapping!E:E),IF(AND(Trans!O:O=Mapping!C:C),Mapping!E:E),IF(AND(Mapping!C:C="",Trans!O:O<>""),Mapping!E:E)

The formula can also be searching by row. I would just like something that works!

Please help.