Thread: use ISNA 3 times with 4 vlookup

I think perhaps something along the lines of:

=IF(E3=E2,0,IF(ISNUMBER(MATCH(E3,OPMS!$E$1:$E$2528,0)),VLOOKUP(E3,OPMS!$E$1:$G$2528,3,0),IF(ISNUMBER(MATCH(E3,OPMS!$D$1:$D$2528,0)),VLOOKUP(E3,OPMS!$D$1:$G$2528,4,0),IF(ISNUMBER(MATCH(E3,RemovedScripts!$D$1:$D$42,0)),VLOOKUP(E3,RemovedScripts!$D$1:$E$42,2,0),IF(ISNUMBER(MATCH(E3,RemovedScripts!$C$1:$C$42,0)),VLOOKUP(E3,RemovedScripts!$C$1:$E$42,3,0),"")))))

that said - if the data type being returned by the VLOOKUP is consistent (ie always number, always text) you could shorten the above (though it would be a smidgeon less efficient), eg if we assume the result is always a number:

=LOOKUP(REPT("Z",255),CHOOSE({1,2,3,4,5},"",VLOOKUP(E3,RemovedScripts!$C$1:$E$42,3,0),VLOOKUP(E3,RemovedScripts!$C$1:$D$42,2,0),VLOOKUP(E3,OPMS!$D$1:$G$2528,4,0),VLOOKUP(E3,OPMS!$E$1:$G$2528,3,0)))

depending on where E3 is found this is potentially less efficient because all 4 VLOOKUPs are performed at all times