LOOKUP to find value that is not zero

[cedarhill]

I have a formula in a worksheet that lists equipment in inventory. It looks at another worksheet of service work orders and verifies that, if work was done on a particular unit, do a look up and post the latest meter reading recorded on a work order for that unit.

My formula works fine unless the latest work order doesn't have a meter reading posted yet, so it returns a zero - overwriting any previous meter reading that was stored there.

=IF(COUNTIF('Work Orders'!$H$3:$H$1500,$C38),LOOKUP(2,1/('Work Orders'!$H$3:$H$1500=$C38),'Work Orders'!$O$3:$O$1500),"")

How do I get the most current reading...as long as it isn't a zero?

[DonkeyOte]

Perhaps you want:

=IF(COUNTIF('Work Orders'!$H$3:$H$1500,$C38),LOOKUP(2,1/(('Work Orders'!$H$3:$H$1500=$C38)*('Work Orders'!$O$3:$O$1500<>0)),'Work Orders'!$O$3:$O$1500),"")

[NBVC]

Possibly?

=IF(COUNTIF('Work Orders'!$H$3:$H$1500,$C38),LOOKUP(2,1/(('Work Orders'!$H$3:$H$1500=$C38)*('Work Orders'!$O$3:$O$1500<>0)),'Work Orders'!$O$3:$O$1500),"")

[cedarhill]

Yes, thank you both, it does exactly what I need it to do.