Verifying Sales Order on two worksheets

  • I have the following formula in my worksheet that is not working:

    =IF(AND('FY03 Booking Detail'!$F$24:$F$65536=D22,'FY03 Booking Detail'!$C$18:$C$65536=C22),"Match","None")

    I have an two columns on two worksheets with the same information - as evidenced by the formula.

    The "FY03 Booking Detail" is the correct worksheet, the active sheet is the copy.

    The return value I am getting why I run this formula is "None". In other words, the And, If then formula is not running properly because when I check the master sheet, the Sales Order is there.

    What can I do to improve the formula?

  • Try Ctrl+Shift+Enter to enter the formula not just Enter...

    Formula should have {} round it if it's to use your ranges as arrays..

    Post back if this doesn't work.... I'v ebeen to the pub for lunch :)

  • The braces did not copy over.

    I do get "None", not the "#NA" or "#Value" error messages.

    For some reason, it is defaulting to the false criteria, ignoring the if then altogether.

    Maybe it is a formating problem where both columns are not formatted properly.

  • By the way - the way you have your formula set up means that this is what you are testing....

    If the contents of range 'FY03 Booking Detail'!$F$24:$F$65536= the value in D22 AND the contents of range 'FY03 Booking Detail'!$C$18:$F$65536= the value in C22 then return the value "Match", otherwise return the value "None"

    So, If the whole range F24:F65536 contains the same value as cell D22 AND the whole range C18:C65536 contains the value in cell C22, you'll get a "Match", otherwise you won't....

    My question, to help you, is:

    What test are you trying to perform?

    I tested my array formula and it worked for the above EG....

  • Your summation of the code is exactly my intent.

    This is why I am confused why it doesn't work because I have used this type of formula before.

    If if works for you, then something must be wrong with my file or formatting.

  • From Bob Umlas from New York I received this following formula:

    =IF(ISNA(MATCH(C56&D56,'FY03 Booking Detail'!$C$18:$C$1000&'FY03 Booking Detail'!$F$18:$F$1000,0)),"None","Match")

    His explanation to me is this:

    "All I can say is that your formula didn't make sense. You can't test to see if C21=some other entire range (which would give a huge # of true/false), you need to see if it MATCHES any of the items. "

    Plus he added this after giving me the formula:
    "which says if there's an error (N/A) matching C21 & D21 (concatenated together) against 1000 rows if the other sheet IN THE SAME ROWS (your formula looked like it was seeing if the -210 existed ANYWHERE and if the A30021-040 existed ANYWHERE, which I don't think you wanted to do -- ), then there's a match".

  • for what it's worth, my singlemost defining moment in Excel was working through an explanation Mr Umlas emailed me to one of his formulas for checking if a number was a prime number, after I asked him how it worked and what on earth it was doing.

    When I understood the lateral thinking he was employing, I was astounded by how much Excel could be used, manipulated and massaged to give you what you want to know.

    :thumbup: @ Mr Umlas

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!