Lookup multiple columns to return value

Good Afternoon all,

I have a quick formula problem. As the title says I'm trying to return a value based on multiple criteria in sheet 1 column C (highlighted). I used the following formula orginally:

=IF(ISNA(VLOOKUP(CONCATENATE(A2,"/",B2),Sheet2!A:D,4,0)),VLOOKUP(A2,Sheet2!B:D,3,0),VLOOKUP(CONCATENATE(A2,"/",B2),Sheet2!A:D,4,0))

However, this only reads one list from sheet 2 as I am not advanced enough to know how to read more than one list lol. I know it seems I could just combine the lists in to one but my actual file has that much data in the lists I have to split them in to multiple columns.

I am known to poorly explain what I mean in these threads so let me know if you need any more information.

many thanks,

JHW