How JustAnswer Works:

  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.

Ask The-PC-Guy Your Own Question

The-PC-Guy
The-PC-Guy, Computer Manager
Category: Microsoft Office
Satisfied Customers: 1873
Experience:  20 years experience providing remote computer support
62934938
Type Your Microsoft Office Question Here...
The-PC-Guy is online now
A new question is answered every 9 seconds

I have a data table that looks like this and I want to look

This answer was rated:

I have a data table that looks like this and I want to look up the total cost from another look up table (further below) The issue is that I want the lookup to use the week start AND week end range and find the appropriate total cost. There is no overlap in the week start/week end ranges in my data. Can you help?:

Data Table:
week start week end income Total Cost
12/4/2013 12/10/2013 $28,107.77 ?
12/11/2013 12/17/2013 $33,498.13
12/18/2013 12/24/2013 $25,381.66
12/25/2013 12/31/2013 $30,650.67

Look-up Table:
Date Sum of Total Cost
1/31/2014 $4,931.25
2/3/2014 $4,931.25
2/10/2014 $3,287.50
2/14/2014 $4,931.25
2/17/2014 $2,705.00
2/22/2014 $1,250.00
2/24/2014 $11,500.00
2/26/2014 $3,493.75

The-PC-Guy :

Thanks for using JustAnswer. My name isXXXXX will do whatever I can to answer your question

The-PC-Guy :

is this an excel file?

Customer:

Yes

The-PC-Guy :

ok, I would like to see an example of your data

The-PC-Guy :

if you click the paperclip icon at the top of this chat

The-PC-Guy :

you should be able to attach an example

Customer:



























































week startweek endincomeTotal Cost
12/4/201312/10/2013 $ 28,107.77?
12/11/201312/17/2013 $ 33,498.13
12/18/201312/24/2013 $ 25,381.66
12/25/201312/31/2013 $ 30,650.67
1/1/20141/7/2014 $ 29,071.71
1/8/20141/14/2014 $ 23,226.94
1/15/20141/21/2014 $ 13,971.13
1/22/20141/28/2014 $ 19,486.17
1/29/20142/4/2014 $ 30,177.69
2/5/20142/11/2014 $ 33,696.64
Customer:

I will try attachment

Customer:

Hmm, not sure I'm following the image attachment instructions.

Customer:

can we work from what I just sent you

Customer:

The sample I sent is the data table and I want to pull from a lookup table the total cost

The-PC-Guy :

what you paste comes as text


 

The-PC-Guy :

if you want the formula to work the actual sheet is best

The-PC-Guy :

ok

The-PC-Guy :

try this site

The-PC-Guy :

www.wikisend.com

The-PC-Guy :

after uploading they give you file ID#

The-PC-Guy :

paste that here

Customer:

The paperclip says its to attach an image not a file

The-PC-Guy :

yes i know

The-PC-Guy :

its bad design

The-PC-Guy :

but it works on files too

Customer:

Send twice cause it didn't look like it worked

Customer:

both are the same

Customer:

can you actually open that?

The-PC-Guy :

lemme check

The-PC-Guy :

ok its open

The-PC-Guy :

so you are loooking for the sum total from the table in columns L and M

The-PC-Guy :

that are within each date range?

Customer:

yes

Customer:

tried vlookup but will only let me pick one of the dates from the table on the left. Don't know how to get it to look at both

The-PC-Guy :

ok

The-PC-Guy :

give me a minute to get it

Customer:

sure

The-PC-Guy :

you can download the modified file from this link

http://ge.tt/7zOTIM01/v/0?c

I hope this is what you need, if not, I can make changes

let me know if you have any questions, comments, or concerns

IF YOU HAVE A MINUTE, PLEASE REMEMBER TO RATE MY SERVICE SO I GET PAID FOR MY TIME
YOU DO SO WITH THE SMILEYS BENEATH THIS CHAT

THANKS

The-PC-Guy :

after clicking the link, click the blue download button to get the file

Customer:

No, not looking for the sum. The cost should just be applied once to the range as is.

Customer:

so for example, the range of 1/29-2/4 should have total of $4,931.25

The-PC-Guy :

let me check that

Customer:

oh, wait you are right

The-PC-Guy :

so you are just looking for a single value for each date range

Customer:

I was told there wasn't any overlap but there is

The-PC-Guy :

Im always right

The-PC-Guy :

but I will do whatever you need

The-PC-Guy :

ok, sounds like this is what you were looking for then

The-PC-Guy :

if you need any modifications you can always let me know

The-PC-Guy :

IF YOU HAVE A MINUTE, PLEASE REMEMBER TO RATE MY SERVICE SO I GET PAID FOR MY TIME
YOU DO SO WITH THE SMILEYS BENEATH THIS CHAT

Customer:

Great - thanks so much!

Customer:

BTW, I think you're service was excellent but your interface for files is poor

The-PC-Guy and other Microsoft Office Specialists are ready to help you