Ok the lookup value, when the first character is a "B" is numeric... ie. Mid(O9,2,4) is supposed to be numeric...

You show that you accounted for that in your spreadsheet by adding the VALUE() function... but didn't show that in your originally posted formula...

anyways... try:

=IF(ISNA(IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(E9="OLD",VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),"")),"")),"")),"",IF(O9<>"",IF(LEFT(O9)="B",VLOOKUP(MID(O9,2,4)+0,COMPSB!A:AP,42,FALSE),IF(E9<>"",IF(E9="New",VLOOKUP(O9,'COMPSW-NS'!D:AP,35,FALSE),IF(E9="OLD",VLOOKUP(O9,'COMPSW-OS'!D:AP,35,FALSE),"")),"")),""))
does that work or are we still missing something..

I don't really get the 190 option though... when exactly is that supposed to display>?