Excel MATCH function not working with automatic data update

Question

I have an excel function that looks like this:

=MATCH($A3,Table[Column],FALSE)+1

What this function does is return me the row number of the matching row where

 table[column] == $A3

and this is working fine in a static file. However as soon as I made the data from Table to automatically update every minute it just stopped working (I get #N/A error as the function return).

could it be you need to trigger a recalc along with your data update? — Peter Albert, Feb 26 '13 at 20:38
@PeterAlbert can you explain a little bit better? i pay with accepted answers :) — KoU_warch, Feb 26 '13 at 20:39
how do you regularly update you data? If in VBA, use `Sheets("YourWorksheet").Calculate`. In normal Excel, simply press `F9`! — Peter Albert, Feb 26 '13 at 20:44
@PeterAlbert BTW, i cant see the recalc function in the spread sheet functions, do i need to involve a vba function? — KoU_warch, Feb 26 '13 at 20:45
The worksheet has autmatic calculation on. hitting the `F9` did not help, i suspect it's a little more complicated than that. — KoU_warch, Feb 26 '13 at 20:46

score 2 · Accepted Answer · answered Feb 26 '13 at 20:41

After imports/updates to tables and pivot tables, you often need to use the Refresh All button in the data toolbar. That should clear this issue right up! I would even create a VBA macro to call the refresh automatically after every update.

I've also found it's better to use named ranges or dynamic named ranges in situations such as this. Check this out: http://support.microsoft.com/kb/830287

Excel MATCH function not working with automatic data update

1 Answers1