Maybe you can use =index(match()) if you can match on unique headers.

Take a look at Debra Dalgleish's notes:
http://www.contextures.com/xlFunctions03.html

nastech wrote:

have an example of formula, as an array that finally works, but wonder if
there is a more dynamic way of find the column I am looking for? thanks

where AP is the compared / start column in the old file, looking for data
in column CI (had to count the columns by hand). Is there a more dynamic
way to calculate the number of columns over, within the formula.

would be will willing to calculate it outside the formula with referenced /
worker cells, entering column letters in: From & To cells.

sample working with is:

=IF(ISNA(VLOOKUP(AQ327,[file.xls]sheet!$AP$1:$CI$3355,46,FALSE)),"",VLOOKUP(AQ327,[file.xls]sheet!$AP$1:$CI$3355,46,FALSE))

--

Dave Peterson