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?
Bookmarks