Vlookup

A

akaramarkovic

Hi,
I'm looking to do a vlookup in a sales report that includes subtotals.
The formula i have come up with thus far is the following:
=VLOOKUP("1822 Total",'[Period 6 sales b
SKU.xls]MFGRP-34_MFR-43152_20050522-200'!$B$29:$I$5643,8,FALSE).
The only problem with this formula is that I have to mannually chang
the "____ Total" part for each corresponding store. Thus I have t
input "122 Total" for store 122 in the worksheet and so on. The 12
portion of the part in quotations is foudn in another cell on th
workseet so I was hoping to program the formula to pick up the stor
number automatically from the cell somethign along the lines o
=VLOOKUP(B4+" Total",'[Period 6 sales b
SKU.xls]MFGRP-34_MFR-43152_20050522-200'!$B$29:$I$5643,8,FALSE). Ca
you please tell me if this is possible and offer any suggestions.
Thank you
 
R

Ron Coderre

You were VERY close:

=VLOOKUP(B4+" Total",'[Period 6 sales b
SKU.xls]MFGRP-34_MFR-43152_20050522-200'!$B$29:$I$5643,8,FALSE).

Should be:
=VLOOKUP(B4&" Total",'[Period 6 sales b
SKU.xls]MFGRP-34_MFR-43152_20050522-200'!$B$29:$I$5643,8,FALSE).

Reason:
The + sign is an arithmetic operator
The & sign is a text concatenator

Example:
="any"&"thing" evaluates to "anything"
while ="any"+"thing" evaluate to #value

Does that help?

Ro
 
A

akaramarkovic

Ron,
You helped me score some major brownie points with my boss!!!
Best regards,
Aleks Karamarkovic
 
Top