the formula will not make it look into different tables it will just choose what value to display form your data set in that row (or group if needed).
you have to join (likely outer joins) you tables together properly and then use the resulting data set to determine your formula.
possibly something like
if isnull(table1.field) and isnull(table2.field) then table3.field else
if isnull(table1.field) and then table2.field else
table1.field