Dear sir,with best regards,i need your help to solve my problem in my sheet which contains this formula(=(LOOKUP(2;1/($O$8:$O$5000=Z15);$Q$8:$Q$5000)),when i pull it down i got (#N/A)against all blank cells,how can i get (0) or ( empty cell) instead of (#N/A).
Many thanks in advance.
Last edited by tofimoon4; 11-25-2009 at 04:02 PM.
If you're saying the #N/A is generated only when content of Z is blank then use a pre-emptive IF
=IF(Z15="";"";LOOKUP(2;1/($O$8:$O$5000=Z15);$Q$8:$Q$5000))
If the #N/A is being generated because the non-blank contents of Z can't be found in O range then you need to clarify for us the data type of the Q range - ie what is being returned by the LOOKUP - numbers / text or a mix ?
(we ask because if the output data type is consistent we can use a secondary LOOKUP to avoid need for double evaluation of the original)
Last edited by DonkeyOte; 11-25-2009 at 05:23 AM. Reason: typo
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
Dear sir,with my appreciated,in(O) column i have (material details)(text) which we sold them daily ( i took them from the supplier list),in (Z) column ihave a scheduale of all the materials (to have details of selling,buying,prices changes)(text also),in(Q) column i have the prices of these materials ( i took them from supplier list)(numbers),in (AE) column i have above formula to get the last price which (contains the problem).
I hope you shall solve this problem .
Perhaps best to simply revert to:
Please Login or Register to view this content.
Thank you sir very much , it works very well .
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks