Vlookup problem

[yclhk]

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,

 

[Dave Peterson]

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]

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,

 

[Dave Peterson]

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]

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,

 

[Dave Peterson]

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.

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,

 

[yclhk]

Thanks, Dave,

How about also matching the 2nd tel no. in the same cell to lookup the
related customer's info if the customer provides the 2nd tel no. for enquiry.

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.

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,

 

[Dave Peterson]

I would use multiple cells instead of using a long formula:

In one cell (say C2), look for a match using the first number:
=vlookup(x1,'[otherworkbook.xls]Sheet1'!$A1:$E33,3,FALSE)

In the adjacent cell (D2), look to see if it returned an error. If it returned
an error, then look for a match in column B (notice the table changed).
if(isna(C2),vlookup(x1,'[otherworkbook.xls]Sheet1'!$b1:$E33,2,FALSE),C2)

(if the C2 didn't return an error, then it's brought over to D2.)

Then hide column C.



Then

Thanks, Dave,

How about also matching the 2nd tel no. in the same cell to lookup the
related customer's info if the customer provides the 2nd tel no. for enquiry.

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.

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,

 

[yclhk]

Thanks Dave, it works after numerous modification of the formulas in
enquiry.xls. Thanks a lot again,

yclhk from Hong Kong

I would use multiple cells instead of using a long formula:

In one cell (say C2), look for a match using the first number:
=vlookup(x1,'[otherworkbook.xls]Sheet1'!$A1:$E33,3,FALSE)

In the adjacent cell (D2), look to see if it returned an error. If it returned
an error, then look for a match in column B (notice the table changed).
if(isna(C2),vlookup(x1,'[otherworkbook.xls]Sheet1'!$b1:$E33,2,FALSE),C2)

(if the C2 didn't return an error, then it's brought over to D2.)

Then hide column C.



Then

Thanks, Dave,

How about also matching the 2nd tel no. in the same cell to lookup the
related customer's info if the customer provides the 2nd tel no. for enquiry.

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,