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 Jess M. Your Own Question

Jess M.
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4256
Experience:  Computer Software Specialist for more than 10 years
49766785
Type Your Microsoft Office Question Here...
Jess M. is online now
A new question is answered every 9 seconds

Im trying to create a vlookup function in excel that performs

Resolved Question:

I'm trying to create a vlookup function in excel that performs a very specific task.
It needs to able to look up the shipment date in a row, then find that date from a range of dates in another workbook, go over 3 columns, get the rate and divide it by 100. It then needs to take that rate, find it from another range of rates in another workbook, and bring back a percentage.
Can you help?
Thanks in advance
Submitted: 1 year ago.
Category: Microsoft Office
Expert:  patrickdengler replied 1 year ago.

patrickdengler :

Hi, I think I can help you here. Are you talking about a single vlookup function or are you using a script?

patrickdengler :

In general, what you will want to do is basically nest these calls (either on after another or in one call)

patrickdengler :

date=Vlookup(Order#,_table,_col), WKB.Table,Col,

patrickdengler :

oops

patrickdengler :

date = VLookup(Order_Or_Row#, Column)
rateRowForDate = VLookup(Date, WorkBook2Column, Date) << Important to put the data twice; that will remove the exact match
FinalRate = rateRowForDate+3 cols / 100

patrickdengler :

If you have a sample of the data, I can write it for you. Just let me know if you want it in a cell or in a macro

Customer:

Hi patrick,

Customer:

I could send you the data but I can't see a way to attach a file anywhere...

Customer:

Are you still there?

patrickdengler :

yes, sorry, I was last on last night (6/26). Can you post it to dropbox.com or skydrive.com?

Customer:

sure, here it is https://dl.dropboxusercontent.com/u/60457349/audit%20example.xlsx

Expert:  Lindie-mod replied 1 year ago.

Hello,

I'm Lindie, and I’m a moderator for this topic. I've been working hard to find a professional to assist you right away, but sometimes finding the right professional can take a little longer than expected.

I wonder whether you're ok with continuing to wait for an answer. If you are, please let me know and I will continue my search. If not, feel free to let me know and I will cancel this question for you. Thank you!

Best,

Lindie

Customer: replied 1 year ago.

Hi Lindie,


 


Ideally we would like an answer today. Is that possible?


 


Thanks,


Diana

Expert:  Lindie-mod replied 1 year ago.

Hello,

Thank you, XXXXX XXXXX continue to look for a professional to assist you. Please let me know if I can be of any further assistance while you wait.

Best,

Lindie

Expert:  Jess M. replied 1 year ago.
Hi, welcome and thank you for your question. My name is XXXXX XXXXX I am glad to assist you today.

I got your sample data. Which column is the shipment date there? Also, where do you want to put the formula? (which column).

One more thing, the formula needs to access another workbook, can you please send a dummy or sample file of that other workbook?


Please let me know so that I can help you further.

Best regards,
Jess
Customer: replied 1 year ago.

Hi Jess,


 


Sorry, I just realized there were some things wrong with the sample i sent you. I meant to include the data from the other workbooks in seperate sheets. I've replaced the old file with the new one, here is the link: https://dl.dropboxusercontent.com/u/60457349/audit%20example.xlsx


 


The shipment date is actually labeled as pickup date. Sorry for the confusion.


 


The formula should go in the yellow column labeled "FSC Amount".


 


Thanks,


Diana

Expert:  Jess M. replied 1 year ago.
Diana,

I got your file. I will now check it for your requirement. I will let you know.

Jess
Expert:  Jess M. replied 1 year ago.

Diana,

Please clarify, for instance in row T506, the date is 6/3/2013 and in the second worksheet, the rate is 3.869. But in the third sheet, there is NO 3.869 there. What must be done in this case that there is no exact lookup result?

 

Or can you share a sample result of the formula so that I can test if the formula I am writing is right?


Regards,
Jess

Customer: replied 1 year ago.

Hi Jess,


 


This is one of the issues we were trying to get around-


most of the rates in the second sheet are not in the third sheet.


The first and second columns in the third sheet create a range that corresponds to the percentage in the same row, and we're trying to see if it's possible to find that rate in the range. Is that possible?


 


Thanks again,


 


Diana

Expert:  Jess M. replied 1 year ago.
Diana,,

I am a bit confused. Are you saying that in the third sheet, the third column which contains % data ARE NOT the ones to be displayed as output in the first sheet's formulas? Can you give me a sample of your desired results so that i can base my work there? I was able to plot a formula but the output was supposed to be the % values in the third column in the third sheet. Please advise.

Regards,
Jess
Customer: replied 1 year ago.

No, you were right, the % data in the third sheet is what needs to be displayed. For example, row 2 should have a percentage of 0%.


I hope that helps.


Thanks,


Diana

Expert:  Jess M. replied 1 year ago.
Diana,

Thank you for that confirmation. Here is the completed file:
http://wikisend.com/download/459204/audit_example_2_jess1.xlsx

In that file you can see that I used the IFERROR function to clear the cells or to make the cells blank IN CASES where there are no matches. If there is no iferror function, then the cells where there are no matches found will be displaying #N/A.

In the actual file that you gave me, which I completed and submitted to you, all of the cells are blank. The reasons for this is because there were NO MATCHES found.

For instance, in row 2, the formula will lookup for the date 5/28/2013 in the second sheet. There are NO dates in the second sheet that is 5/28/2013, there is only one 5/27/2013. So in this case, all rows with dates 5/28/2013 will show blank.

The logic or flow of the formula that you described, is this:
  1. Excel will lookup the date in column T in the first sheet inside the second sheet.
  2. When a match is found, take column E of the second sheet as the result.
  3. With this result (data in column E in the second sheet), lookup for this rate in the third sheet and lookup IN the first column, A.
  4. When a match is found, display the percentage in the third column, C, as a result.

That was the logic I used to create the formula in the file I gave you above. However, in the looped vlookups, there were no matches found so all cells in column V (Invoice) are blank. I manually checked if there are matches, and there are really NO matches.

 

The only thing that will produce matches is when we use Column B in the third sheet as the lookup column and we will temporarily change the date in the second sheet from 5/27/2013 to 5/28/2013 in cell B22 to produce a match to test the logic in the formula.

 

Here is the completed file:

http://wikisend.com/download/604800/audit_example_2_jess2_colB.xlsx

 

In either formula used in the 2 files I gave you, as long as there are matches in the actual data that you have there, the percentages will be shown. If not, then the cells will be blank because of the iferror function.

 

Please remember to rate my service positively (3-5 stars/faces) if this helped. This is the only way that I can get credit for my work. Tips are always highly appreciated! Cool

If you have any other questions, please ask me or reply to me – I’ll be happy to respond and help you further.

Thank you!

Best regards,
Jess

Customer: replied 1 year ago.

Hi Jess,


 


There's just one problem with the formula you sent. We need it to look between the dates that are entered in the Fuel Surcharge History tab (vertically), as opposed to matching the exact date. Similar to the range in the Fuel Surcharge tab (horizontally). So, find the price of diesel in the fuel surcharge history table, then find the corresponding percent charge (column 3) in the fuel surcharge tab.


 


Does this make sense?


 


If you can figure this out, we'll give you a bonus.

Expert:  Jess M. replied 1 year ago.

Diana,

This is a new information. What did you mean by "We need it to look between the dates that are entered in the Fuel Surcharge History tab (vertically), as opposed to matching the exact date."?

Can you give an example for an specific date? By vertically, are you saying something like, if the date is 5/27/2013, all dates after 5/27/2013 will be a match?

 

You mentioned "So, find the price of diesel in the fuel surcharge history table, then find the corresponding percent charge (column 3) in the fuel surcharge tab." That is actually what the formula is doing. However, what is your criteria in selecting WHICH price of diesel is to be selected in Fuel Surcharge History tab based on the date in the Invoice tab if it is not an exact match?

Please advise,
Jess

Customer: replied 1 year ago.

Hi Jess,


 


Yes, that is pretty much what we are looking for. If the date is 5/27/2013, all dates between that date and 6/03/2013 should be a match. Does that make sense? Sorry I didn't explain that earlier.


 


I hope this helps.


 


Diana

Expert:  Jess M. replied 1 year ago.

Diana,

The result will be displayed in only one cell. How do you suppose to display that multiple matches? Do you want to filter that further to result in only one value? In the NEXT calculation step, only ONE rate will be used to lookup in the fuel surcharge sheet so the prior step should result to one value only.

Please advise.

Jess

Expert:  Jess M. replied 1 year ago.
Diana,

I think I am understanding it. Are you saying that the lookup shall be "within" a given week and not just with an exact date match?

Please advise.

Jess
Customer: replied 1 year ago.

Yes, that is exactly what we need. Can you do that?


 


Thanks,


 


Diana

Expert:  Jess M. replied 1 year ago.
Diana, thank you for that confirmation. Please give me some time to come up with this exact file. I will get back to you with the results.

Best regards,
Jess
Customer: replied 1 year ago.

Hey Jess,


I haven't heard from you in a couple days. If you're having any issues, please let me know.


Thanks,


Diana

Expert:  Jess M. replied 1 year ago.
Hi Diana,

Thank you for writing back.This one is really tricky but after some time of testing, I was able to come up with the right formula. I used the WEEKNUM formula to test column T in the Invoice sheet. The result will be the week number similar to column A in Fuel Surcharge History Sheet. Please note of the weeknum formula in action in the last column I added in the Fuel Surcharge History sheet to indicate the actual week numbers using the formula.

Now, I used that week number in the lookup. The effect is, regardless of the actual date in column T, it will be assigned to its actual week number equivalent to the week numbers in column A in the Fuel Surcharge History sheet.

Again, the same logic is used here based on your request. It is a nested vlookup but this time, the lookup data is the week number not the date in column T.

However, since there are NO actual matching data, the results are still blank. But if you apply the formula in your actual data there where there will be actual matches, it shall give you your desired results.

Here are the download links:

In the formula in this file, the basis for lookup in the third sheet is the first column, A:
http://wikisend.com/download/105434/audit_example_2_jess2_colA.xlsx

In the formula in this file, the basis for lookup in the third sheet is the second column, B:
http://wikisend.com/download/387374/audit_example_2_jess2_colB.xlsx

Please remember to rate my service positively (3-5 stars/faces) if this helped. This is the only way that I can get credit for my work. Tips are always highly appreciated! Cool

If you have any other questions, please ask me or reply to me – I’ll be happy to respond and help you further.

Thank you!

Best regards,
Jess
Customer: replied 1 year ago.

Jess,


We really need this formula to look between ranges of numbers or dates.


Could we convert the date to a whole number and write a formula? For example, the first day of January is December 30th, the Second day is December 31st, the third day January 1st, so forth and so on.


In other words, if we use whole numbers instead of dates, can we do this?


Thanks,


Diana

Expert:  Jess M. replied 1 year ago.
Hi Diana,

There is no problem with the lookup of dates here in column T. It is already addressed in the formula that I gave you. It is now looking up BETWEEN ranges of dates, this is done using the weeknum function. In the file that I will give you below, I completed the 52 weeks in the second sheet, you just have to fill up with values (rates) in the future dates.

Now, the only one issue here WHY we are not getting any % displayed in FSC Amount is because THERE ARE NO actual matches. I hope you figured that out.

Let us take data for instance. In Invoice sheet, cell Y2. The date is 5/28/2013 and it will lookup this date in column 23 in the Fuel Surcharge History sheet since it is in week 22. That is the range now that you are referring to, each date in column T (Invoice) will always fall in a particular week where the Price is looked up. In Y2 therefore, the price to be used is $3.869. Now, we will use this value and look it up in the third sheet. Unfortunately, NOT a single cell in the third sheet either in column A or B contains $3.869. As a result, the correspoding cell in sheet un under FSC Amount will be blank, no percentage to display.

So as you can see, the problem lies on your actual data in sheet 2 (surcharge history) and sheet 3 (fuel surcharge) because the price in column E does NOT HAVE exact matches in columns A or B in the third sheet.

To show you what I really meant here, I added to columns in the second sheet (Fuel Surcharge History) to show the lookup results for the PRICE column and column A and B in the third sheet. This is just to show you the actual matches and it only shows 2 matches to display 0.00%. Why they did not show up in the FSC Amount column? Simply because they did not match - they are NOT in the right WEEK.

Or do you want to limit the lookup by rounding the prices and rates to 2 decimal places? In this way, they can surely get a match. But I believe you really need to match the prices and the rates of up to 3 decimal places.

Here is the file:
http://wikisend.com/download/241580/audit_example_2_jess2_colA.xlsx

I hope this explains the scenario.

Please remember to rate my service positively (3-5 stars/faces) if this helped. This is the only way that I can get credit for my work. Tips are always highly appreciated! Cool

If you have any other questions, please ask me or reply to me – I’ll be happy to respond and help you further.

Thank you!

Best regards,
Jess
Jess M., Computer Support Specialist
Category: Microsoft Office
Satisfied Customers: 4256
Experience: Computer Software Specialist for more than 10 years
Jess M. and 3 other Microsoft Office Specialists are ready to help you

JustAnswer in the News:

 
 
 
Ask-a-doc Web sites: If you've got a quick question, you can try to get an answer from sites that say they have various specialists on hand to give quick answers... Justanswer.com.
JustAnswer.com...has seen a spike since October in legal questions from readers about layoffs, unemployment and severance.
Web sites like justanswer.com/legal
...leave nothing to chance.
Traffic on JustAnswer rose 14 percent...and had nearly 400,000 page views in 30 days...inquiries related to stress, high blood pressure, drinking and heart pain jumped 33 percent.
Tory Johnson, GMA Workplace Contributor, discusses work-from-home jobs, such as JustAnswer in which verified Experts answer people’s questions.
I will tell you that...the things you have to go through to be an Expert are quite rigorous.
 
 
 

What Customers are Saying:

 
 
 
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
< Last | Next >
  • My Expert answered my question promptly and he resolved the issue totally. This is a great service. I am so glad I found it I will definitely use the service again if needed. One Happy Customer New York
  • Wonderful service, prompt, efficient, and accurate. Couldn't have asked for more. I cannot thank you enough for your help. Mary C. Freshfield, Liverpool, UK
  • This expert is wonderful. They truly know what they are talking about, and they actually care about you. They really helped put my nerves at ease. Thank you so much!!!! Alex Los Angeles, CA
  • Thank you for all your help. It is nice to know that this service is here for people like myself, who need answers fast and are not sure who to consult. GP Hesperia, CA
  • I couldn't be more satisfied! This is the site I will always come to when I need a second opinion. Justin Kernersville, NC
  • Just let me say that this encounter has been entirely professional and most helpful. I liked that I could ask additional questions and get answered in a very short turn around. Esther Woodstock, NY
  • Thank you so much for taking your time and knowledge to support my concerns. Not only did you answer my questions, you even took it a step further with replying with more pertinent information I needed to know. Robin Elkton, Maryland
 
 
 

Meet The Experts:

 
 
 
  • jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
< Last | Next >
  • http://ww2.justanswer.com/uploads/JS/jstinehelfer/2012-6-6_215146_Pictureofme1.64x64.png jstinehelfer's Avatar

    jstinehelfer

    Information Systems Manager

    Satisfied Customers:

    36
    A+ Comptia Certified computer repair
  • http://ww2.justanswer.com/uploads/JA/JasonJames122/2011-12-16_135647_jasonjamesheadshotweb.64x64.jpg JasonJames122's Avatar

    JasonJames122

    Computer Enthusiast

    Satisfied Customers:

    0
    I manage all information systems (network of Windows PC's and servers with Microsoft Office products) for a small business
  • http://ww2.justanswer.com/uploads/JE/jessmagz/2012-6-6_18129_jm.64x64.jpg Jess M.'s Avatar

    Jess M.

    Computer Support Specialist

    Satisfied Customers:

    301
    Computer Software Specialist for more than 10 years
  • http://ww2.justanswer.com/uploads/CH/chutz747/2011-11-6_14231_me.64x64.jpg The-PC-Guy's Avatar

    The-PC-Guy

    Computer Manager

    Satisfied Customers:

    274
    20 years experience providing remote computer support
  • http://ww2.justanswer.com/uploads/SP/spherrod/2012-6-6_174244_1000852.64x64.JPG Steve Herrod's Avatar

    Steve Herrod

    Computer Support Specialist

    Satisfied Customers:

    125
    Trained in Microsoft Office from 2000 through to latest version 2010.
  • http://ww2.justanswer.com/uploads/SE/Seashore2011/2011-12-6_33418_111205223015.64x64.jpg Chris L.'s Avatar

    Chris L.

    Support Specialist

    Satisfied Customers:

    124
    Certified Expert with over 10 years experience.
  • http://ww2.justanswer.com/uploads/KR/krkljatorm/2012-6-24_153417_ITMiro.64x64.jpg IT Miro's Avatar

    IT Miro

    Computer Scientist

    Satisfied Customers:

    97
    Bachelor's Degree in Information Technology, Microsoft Certified Professional
 
 
 

Related Microsoft Office Questions