If you're matching on the first telephone number:

=vlookup(x1,'[otherworkbook.xls]Sheet1'!$A1:$E33,3,FALSE)

Will return the 3rd column (name) from the table.

yclhk wrote:

Hi, Dave,

I have a idea to reduce the number of rows of the data.xls. In the

data.xls, it contains the customers' information in columns : i.e. Tel No.,

Name, Flat No., Floor, Block/Tower, Address, District, ...... In

enquiry.xls, when input the Tel. No., the related customer's info would list.

However, some customers provided two tel. nos for regristation, in the

current data.xls, I create two rows of duplicated records for each tel no.

If the data.xls is modified to columns arrangement Tel. No1, Tel No2, Name,

Flat No., Floor, ........, the data rows can be reduced a lot. But, how can

I set the lookup formula in enquiry.xls to lookup the tel nos. in a cell and

return the required customer's info.

Thanks for your help,

:

I think the limit is dependent on what resources are available on your pc and

how much excel needs.

Debra Dalgleish has lots of notes

http://www.contextures.com/xlFunctions03.html (for =index(match()))

I would have bet that having both workbooks open would resolve it. Sorry I was

wrong.

yclhk wrote:

Thanks Dave,

I tried to open the data.xls before enquiry.xls, it can't solve the problem.

I was now working to modify the vlookup formulas in enquiry.xls, including

limiting the column in the lookup table to the range of data. It still can't

solve the problem.

Since I'm not familar with using the =index(match()) formula, I'm now

working to change the vlookup formula to =index(match()) formula. However,

some more data needed to add to the data.xls regularly, is it with the limit

to lookup the data rows in excel ? Or, some other way to solve the problem ?

Thanks again in advance,

:

Maybe you could open the data.xls before you open the enquiry.xls workbook. If

the "sending" workbook is open, I bet it'll be less intense for excel.

And (just a silly guess), if you're bringing back the 13th column with the

=vlookup() formula, maybe(???) using =index(match()) would be less stressful.

And without seeing your formula, maybe you're using the whole column as the

lookup table. Maybe limiting the range would be better, too.

yclhk wrote:

I have two linked excel files, enquiry.xls and data.xls

The enquiry.xls is with the vlookup formula to lookup the data in data.xls.

It works OK, however, when the data in data.xls upto row 5346, the lookup in

enquriy.xls returns with the error message stating that the memory is not

enough. There are 13 column (i.e. col A to M) in data.xls, and my PC is

running Windows XP SP2, Office 2000 (Chinese) and with 1GB ram on board.

What is the problem caused the error and how to correct it ?

Thanks,