Lookup & Concatenate problem

MOB

Well-known Member
Joined
Oct 18, 2005
Messages
1,055
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

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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..............
 
Upvote 0
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
 
Upvote 0
It doesn't seem to make any difference whether the SalesDataBase workbook is open or closed - I still get the error message

Bugger!
 
Upvote 0
Is your concatenated lookup value found in column A of the lookup table?
 
Upvote 0
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?
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top