Hi Pete,

Thanks for the response. This won't solve my problem. The X value

changes

between "Local Currency" and "USD". As a result, I have a source

document

with values for both Local and USD.

The report allows users to choose between the two and the Vlookup

formulas

pull in the correct number. That's the reason for the IF statement.

Here's the actual formula in all it's glory.

NOTE: $T$5 can change between USD and Local Currency.

IF($T$5="USD",(VLOOKUP($A14,'[Jan Sport Perf USD.xls]4 Sport

Performance'!$J$7:$K$18900,2,FALSE)/1000),(VLOOKUP($A14,'[Jan Sport

Perf

USD.xls]4 Sport Performance'!$H$7:$I$18900,2,FALSE))/1000)

Thanks again,

Brian

Pete said:

Why don't you amend the formula to read:

=IF(a1="x",VLOOKUP 18k rows,2,FALSE),"")

and copy down? Then filter that column for (Blanks) and while the

filter is on enter in the topmost visible cell the other half of your

formula:

=VLOOKUP(18k rows,3,FALSE)

Copy and paste this formula down into the blank cells visible under

the

filter, then remove the filter.

Alternatively in one column (assume X):

=IF(A1="x",2,3) and copy down. Then in Y:

=VLOOKUP(18k rows, X1,FALSE)

and copy down. If resources are tight, you might like to fix the

values

in X (and in other columns) first.

Hope this helps.

Pete