Lookup & Concatenate problem

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,042
Office Version
  1. 365
Platform
  1. Windows
Hi

I have a spreadsheet with the following formula;

=VLOOKUP($C$2&$C28,'[SalesDatabase2006.xls]2006'!$A$5:$AX$5000,F$26,FALSE)

Where $C$2&$C28 is a concatenation of two cells, both cells being formula's (eg C2 is "BPA" C28 is "Consultancy")

$A$5:$AX$5000 is on a large spreadsheet (35mb) and is a concatenated text result, that is supposed to match the concatenation above.

F$26 contains a "column number" for the lookup to use.

I am getting loads of #N/A error messages and I am assuming that Excel is having difficulty matching the 2 concatenations. I have checked that they match, and on the face of it they do, but there is obviously a problem.

Unfortunately I inherited this spreadsheet...........can anyone suggest a better way of getting this lookup to work properly?

Thanks
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi Mob

I think the easiest thing to do is to take on value which returns a #NA and do an EXACT comparison to what you think its counterpart is on the Sales Database:

=EXACT($C$2&$C28,'[SalesDatabase2006.xls]2006'!$F$5)

and thus check that it's returning TRUE. If not, then they don't match (maybe because of Leading/Trailing spaces).

Best regards

Richard
 

kevin.philips

Active Member
Joined
Jan 7, 2003
Messages
364
Hi

Can you post some example data using the HTML Sheet Maker so that we can see.

Regards

Kevin
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,042
Office Version
  1. 365
Platform
  1. Windows
Hi Mob

I think the easiest thing to do is to take on value which returns a #NA and do an EXACT comparison to what you think its counterpart is on the Sales Database:

=EXACT($C$2&$C28,'[SalesDatabase2006.xls]2006'!$F$5)

and thus check that it's returning TRUE. If not, then they don't match (maybe because of Leading/Trailing spaces).

Best regards

Richard

Thanks for the reply

I have checked and I do get a true response.

However, if I go into that formula above and press enter, I get a 10 second delay then this error message "Excel cannot complete this task with available resources. Chosse less data or close other applications".

So do you think my issue might be that the formula is ok, but not updating because of the above message?

And any ideas why I am getting this message - my PC has 1 gig of RAM and I only have Excel and Outlook open..............
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

I guess it could be - I'm afraid I don't know for sure though. Does it make any difference if you save and close the SalesDataBase workbook before you hit recalculate on your other workbook? ie so SalesDataBase should be recalculated and saved and then closed down (make sure it conains no error values itself), then run with the other workbook.

Richard
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,042
Office Version
  1. 365
Platform
  1. Windows
It doesn't seem to make any difference whether the SalesDataBase workbook is open or closed - I still get the error message

Bugger!
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828

ADVERTISEMENT

Is your concatenated lookup value found in column A of the lookup table?
 

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,042
Office Version
  1. 365
Platform
  1. Windows
Yes it is

I've had a quick look on the internet for my error message and it doesnt have anything to do with size of RAM etc, its the memory that Excel allocates that seems to be my problem.

Anyione got any experience of this kind of problem?
 

Forum statistics

Threads
1,141,842
Messages
5,708,919
Members
421,598
Latest member
NewHere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top