VLOOKUP conditional flow equivalent in DAX

Recently I was browsing an Excel forum and someone asked an interesting question. How can one write a formula in which a value must be found in a lookup table, and if none is found then instruct the expression to retrieve a value from another lookup table?