Results 1 to 3 of 3

Array Formula not updating correctly

Threaded View

Merik Array Formula not updating... 10-10-2011, 11:23 AM
daddylonglegs Re: Array Formula not... 10-10-2011, 11:27 AM
Merik Re: Array Formula not... 10-10-2011, 11:31 AM
  1. #1
    Registered User
    Join Date
    10-10-2010
    Location
    New Jersey
    MS-Off Ver
    Excel 2003
    Posts
    11

    Array Formula not updating correctly

    Look at the attached file please. As you can see in cell F2, I want the match the D2&E2 pair with all the A&B pairs to see if it exists on the left; it yes, I want to return 1, otherwise I want to return 0. For example, "John2" should match (because on line 8 we also have John and 2), and "John10" should not match and I should get a zero.

    I create an Array Formula which works in F2. If I type a similar formula manually in F3 (with reference to D3 and E3), it works properly and returns zero. However, If I drag the bottom-right corner of F2 and pull it down to generate the formula in F3 automatically, it gives me a 1, which is the wrong result!

    In fact, if I pull the corner of F2 down to generate the formula in F3, it generates the correct formula! It is only the result that is wrong. If I click on the FX bar up there and hit CTRL+SHIFT+ENTER again, the result is updated to 0.

    Is there a way to populate array formulas, but have them update their values automatically?
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1