help needed to write a vlookup or match formula

[spittingfire]

Hi there I have the following issue I need some help with (see attachment)

I have a list in column A sheet4
A table in Sheet3 B2:C11
Another table in Sheet2 B2:C11

I currently have the following formula in S2 "=IF(Q2="","",VLOOKUP(Q2,Sheet2!$B$2:$C$11,2,TRUE))" that work well. Now I need to modify it or re-write it for the below conditions.

What I am looking to do is write a formula in S2

I want to lookup a value in L2 against a list in column A sheet4.

If a match is found then lookup Q2 in the table in sheet3 B2:C11 column C

If a match is not found then lookup Q2 in table in sheet2 B2:C11 column C

Any assistance will be greatly appreciated.

Thanks in advance