Below is the formula (spaced to make the IFs easier to read).

=IF(
AND(AND(ISTEXT(B4),NOT(B4="")),AND(ISTEXT($B$1),NO T($B$1=""))),
==TRUE
IF($B$1="IS_weap_list",
==#NAME?
IF(HasFormula(VLOOKUP(B28,IS_weap_table,MATCH("Dam ",IS_weap_head,0),0)),
==TRUE
UseSameAs(VLOOKUP(B28,IS_weap_table,
MATCH("Dam",IS_weap_head,0),0)), ==NAME?
VLOOKUP(B28,IS_weap_table, MATCH("Dam",IS_weap_head,0),0)),
==NAME?
IF(HasFormula(VLOOKUP(B28,CL_weap_table,
MATCH("Dam",CL_weap_head,0),0)), ==#VALUE!
UseSameAs(VLOOKUP(B28,CL_weap_table,
MATCH("Dam",CL_weap_head,0),0)), ==#VALUE!
VLOOKUP(B28,CL_weap_table, MATCH("Dam",CL_weap_head,0),0))),
==#N/A
"")
==""

The functions HasFormula, and UseAs are UDFs from
http://www.mvps.org/dmcritchie/excel/formula.htm They are inserted in their
own module.
The result of the formula is #Value

The following data is in the cells referenced.
B1 = IS_weap_list
B28 = ER Large Laser
The lookup retuns a formula under "Dam"


The formula:
=IF(ISTEXT(B5),
IF($B$1="IS_weap_list",VLOOKUP(B5,IS_weap_table,MA TCH("Dam",IS_weap_head,0),0),
IF($B$1="CL_weap_list",VLOOKUP(B5,CL_weap_table, MATCH("Dam",
CL_weap_head,0),0),"")),"")


Returns the proper value of 8