How to Make VLOOKUP Formula Dynamic

Hi Guys,

How do I repeat the second part of this formula, like for example if the column does not contain ''Mid-Summer'', I want it to then check if it contains ''Wax-Man'' for example, then for it to VLookup the area for waxman or mid-summer depend on what what it contains.

This is the formula I'm using but it does not work. The second part is in red that I want it to repeat.

=IF(ISNUMBER(FIND("Mid-Summer",B1)),VLOOKUP(B7,A8:B8,2,0),ISNUMBER(FIND("Wax-Man",B1)),VLOOKUP(B4,A5:B5,2,0))

Thanks