Hi Rick,

With your formula you don't actually look up any additional data, you just

return the search argument you used. I suppose there are other formulas

where you look up a second (or other column).

I also suppose the A column is a product number or other unique

identification.

If you would sort the data ascending on the productnumber, you can instruct

Excel to use an intelligent search (like binary search) insted of just

looking through 20K product nembers. That could easily be a 100 times or

more faster.

=VLOOKUP(A3,'C:\Users\rickj\Documents\Master Price

Files\[master.xlsx]Main'!$A$3:$AS$19080,1,TRUE)

You'd then have to test wheter it returned a correct productnumber and if

so, use almost the same formula, but with a different column number,

otherwise return an error value.

I don't think combining all the data in one file should lead to such a large

file. Advantage is that your formula becomes less complex and you can use a

defined name instead of long sheet and cell references.

let's say that you define a name of "MyTable"to

'C:\Users\rickj\Documents\Master Price

Files\[master.xlsx]Main'!$A$3:$AS$19080.

Then your formula to look up the second column would be:

=IF(A3=VLOOKUP(A3,MyTable,1),VLOOKUP(A3,MyTable,2),"ERROR")

--

Kind regards,

Niek Otten

Microsoft MVP - Excel

Rickoshae said:

formula is as follows:

=VLOOKUP(A3, 'C:\Users\rickj\Documents\Master Price

Files\[master.xlsx]Main'!$A$3:$AS$19080, 1, FALSE)

The data is not sorted in any manner...(actually, what do you mean by

sorted?)

Niek Otten said:

Hi Rick,

Show your formula

Is the data sorted?

--

Kind regards,

Niek Otten

Microsoft MVP - Excel