use ISNA 3 times with 4 vlookup

[chrisnaburger]

Good afternoon.

I am trying to vlookup 1 cell in 4 different places. Thought I could use the following formula, but is keeps coming up with an error.

=IF(E3=E2,0,IF(ISNA(VLOOKUP(E3,OPMS!$E$1:$AH$2528,3,FALSE)),IF(ISNA(VLOOKUP(E3,OPMS!$D$1:$AH$2528,4,FALSE)),IF(ISNA(VLOOKUP(E3,RemovedScripts!$D$1:$S$42,2,FALSE)), VLOOKUP(E3,OPMS!$D$1:$AH$2528,4,FALSE ),VLOOKUP(E3,OPMS!$E$1:$AH$2528,3,FALSE),
VLOOKUP(E3,RemovedScripts!$C$1:$S$42,3,FALSE),VLOOKUP(E3,RemovedScripts!$D$1:$S$42,2,FALSE)))))

Any idea what is wrong with the formula?
Can I not use ISNA 3 times. If I can use ISNA 3 times, then have I put them in the wrong place.

Any advise is appreciated.

[DonkeyOte]

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