Logic Function Question

[ngero]

Hi Everyone,

I have this formula that I use to pull a certain data while referencing certain cells.... Here is the formula:



{=IF(B419="","",IFERROR(INDEX('Work Orders'!N:N,MATCH(1,IF(VALUE(D419)='Work Orders'!AC:AC,IF("Canceled"<>'Work Orders'!K:K,IF('Work Orders'!AB:AB<=VALUE(B419),1))),0)),"No Work Order Type"))}

My issue is, it's pulling the mist match it sees and I need it to pull the closest date to B419... So lets say B419 is 06/20/2019 but the formula pulls a WO that is 05/01/2019 becuase it's the first one on the directory that has the same property code....

How can I insert a nested criteria that tells the formula to grab the most recent work order closet to B419(06/24/2019)?

Thank you!

-Nik

 

[Marcelo Branco]

Welcome to Mr Excel forum

Thoughts
1. For the sake of performance avoid references to entire columns in array formulas. Use the max number of expected (likely) rows in your actual scenario.
Something like
=IF(B419="","",INDEX("Work Orders'!N$2:N$1000, MATCH(1,IF(VALUE(D419)='Work Orders'!AC$2:AC$1000,....
1000 is arbitrary - adjust to fit with your real case

2. I would use a helper cell (say Z419) to get the most recent WO closest to B419 (06/24/2019)
Something like
Z419
=MAX(IF(VALUE(D419)='Work Orders'!AC$2:AC$1000,IF("Canceled"<>'Work Orders'!K$2:K$1000,IF('Work Orders'!AB$2:AB$1000<=VALUE(B419),'Work Orders'!AB$2:AB$1000))))
Ctrl+Shift+Enter

3. That said, the main formula would be
=IF(B419="","",IFERROR(INDEX('Work Orders'!N$2:N$1000,MATCH(1,IF(VALUE(D419)='Work Orders'!AC$2:AC$1000,IF("Canceled"<>'Work Orders'!K$2:K$1000,IF('Work Orders'!AB$2:AB$1000=Z419,1))),0)),"No Work Order Type"))
Ctrl+Shift+Enter

Hope this helps

M.

 

[ngero]

Welcome to Mr Excel forum

Thoughts
1. For the sake of performance avoid references to entire columns in array formulas. Use the max number of expected (likely) rows in your actual scenario.
Something like
=IF(B419="","",INDEX("Work Orders'!N$2:N$1000, MATCH(1,IF(VALUE(D419)='Work Orders'!AC$2:AC$1000,....
1000 is arbitrary - adjust to fit with your real case

2. I would use a helper cell (say Z419) to get the most recent WO closest to B419 (06/24/2019)
Something like
Z419
=MAX(IF(VALUE(D419)='Work Orders'!AC$2:AC$1000,IF("Canceled"<>'Work Orders'!K$2:K$1000,IF('Work Orders'!AB$2:AB$1000<=VALUE(B419),'Work Orders'!AB$2:AB$1000))))
Ctrl+Shift+Enter

3. That said, the main formula would be
=IF(B419="","",IFERROR(INDEX('Work Orders'!N$2:N$1000,MATCH(1,IF(VALUE(D419)='Work Orders'!AC$2:AC$1000,IF("Canceled"<>'Work Orders'!K$2:K$1000,IF('Work Orders'!AB$2:AB$1000=Z419,1))),0)),"No Work Order Type"))
Ctrl+Shift+Enter

Hope this helps

M.

What do you mean by helper cell?

 

[Marcelo Branco]

What do you mean by helper cell?

Any empty cell used to calculate an intermediate result to simplify the main formula.


.
M.