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!

Register Yourself Login