NA Response

[Figjam]

Please can someone assist, I don't think I'm a million miles away from the correct formula but I'm obviously missing something! In my formula I'm trying to combine a IF(AND with a VLOOKUP(CONCATENATE but get the return of NA, which generally I would use ISNA or IFERROR but unsure how this fits in combining the two types above? Please help - full formula is -
=IF(AND(VLOOKUP(CONCATENATE(W$6,$A9),'[MRP DATA 2013 v1.xlsx]Work Orders'!$M:$O,2,FALSE)>0),( VLOOKUP(CONCATENATE(W$6,$A9),'[MRP DATA 2013 v1.xlsx]Work Orders'!$M:$O,2,FALSE)), IF(AND(VLOOKUP(CONCATENATE(W$6,$A9),'[MRP DATA 2013 v1.xlsx]Work Orders'!$M:$O,2,FALSE)=<0, (VLOOKUP(CONCATENATE(W$6,$A9),'[MRP DATA 2013 v1.xlsx]Blending Plan'!$C:$D,2,FALSE)>0), (VLOOKUP(CONCATENATE(W$6,$A9),'[MRP DATA 2013 v1.xlsx]Blending Plan'!$C:$D,2,FALSE)-(VLOOKUP(CONCATENATE(W$6,$A9),'[MRP DATA 2013 v1.xlsx]WO Completed'!$K:$L,2,FALSE))</SPAN>

 

[Wookiee]

Welcome to Mr Excel, Figjam!

I have to say, that's a lot of evaluating for one formula.

My first suggestion would be to change your CONCATENATE(W$6,$A9) to simply W$6&$A9 and change the FALSEs in your VLOOKUP formula to 0s. That would save you over 100 characters alone.

Second, is it feasible to store your lookup values in hidden helper columns and just have your formula reference them from the same worksheet? If not, I would suggest using Dunn's V function. You can find the code and an example of its use in this thread (see specifically posts #5 and #10):

http://www.mrexcel.com/forum/excel-questions/676833-using-iferror-conjunction-array-formula.html

I think if you try incorporating some of that, it will make the addition of IFERROR or ISNA simpler.

 

[Figjam]

Thanks Wookie,

I definately change those in my formulas straight away, I presume they are simplier and therefore quicker for the process????

I'll take a look at the posts.

Thanks

Figjam

 

[Wookiee]

You're welcome, Figjam. Although I'm not sure whether those changes will improve processing speed, they certainly do save some room in the formula bar.

Good luck!