Hi, I think I can help you here. Are you talking about a single vlookup function or are you using a script?
In general, what you will want to do is basically nest these calls (either on after another or in one call)
date = VLookup(Order_Or_Row#, Column)rateRowForDate = VLookup(Date, WorkBook2Column, Date) << Important to put the data twice; that will remove the exact matchFinalRate = rateRowForDate+3 cols / 100
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
I could send you the data but I can't see a way to attach a file anywhere...
Are you still there?
yes, sorry, I was last on last night (6/26). Can you post it to dropbox.com or skydrive.com?
sure, here it is https://dl.dropboxusercontent.com/u/60457349/audit%20example.xlsx
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!
Ideally we would like an answer today. Is that possible?
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,
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".
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?
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?
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.
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:
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! 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
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.
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
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,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
Yes, that is exactly what we need. Can you do that?
I haven't heard from you in a couple days. If you're having any issues, please let me know.
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?