• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help getting a blank or zero result with formulas

B

brianaclark1967

Member
=IFERROR(INDEX(Sales!J:J,MATCH(Transport!E:E,Sales!C:C,0)),0)


When I use this formula I get a result of 15, when really it should be zero; because

There is nothing in the column E:E that would give a result of 15.

Can I add something to make it return a zero when nothing is found


ALSO

=HLOOKUP(D2492,Q2491:Z2504,2,FALSE)


how can I get this formula to put a zero as a result if the formula is referring to empty cells for future use; instead of the hash #N/A result

Many thanks

Brian
 
Hi:

Try
=IFERROR(INDEX(Sales!J:J,MATCH(Transport!E:E,Sales!C:C,0),1),0)
=IFERROR(HLOOKUP(D2492,Q2491:Z2504,2,FALSE),0)
 
if you have any blank cells in Column E and any row in Column C contains a 0 you will get a return from the match in your formula in J for that row number which could explain why you are getting a result of 15

you could wrap a if statement around the match and it stops this happening ... without a spreadsheet its hard to tell how it affects the rest of your requirements ... i tried it on a sample and it worked
if(E:E="","",MATCH(E:E,C:C,0))
copy this into your formula and see how you get on
 
Your index forumla of:
=IFERROR(INDEX(Sales!J:J,MATCH(Transport!E:E,Sales!C:C,0)),0)

Should be like
=IFERROR(INDEX(Sales!J:J,MATCH(Transport!E1,Sales!C:C,0)),0)

I'm note sure what cell reference E1 should be

I'd also recommend limiting the rows to something like:
=IFERROR(INDEX(Sales!J1:J10000,MATCH(Transport!E1,Sales!C1:C10000,0)),0)

That formula uses 1/100th of the rows of the original formula and will be so much faster
 
You must log in or register to reply here.
Back
Top