Thread: =MATCH error when using array reference

Trouble with array reference across worksheets:

'=MATCH($A$2,'Jan08'!B1:B100,0)'
returns the correct row number (19), but
'=MATCH($A$2,I4,0)' where I4 is 'Jan08!b1:b100' in general format
returns a '#N/A' error.

When I look at the bad formula with the 'Function Arguments' window, the 'lookup_value' and 'match_type' both evaluate correctly but 'lookup_array' gives a '#VALUE!' error.

Any ideas on why using the actual array works but the array reference doesn't?

use indirect
=MATCH(A2,INDIRECT(I4),0)