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., Computer Support Specialist

Category: Microsoft Office

Satisfied Customers: 5963

Experience: Computer Software Specialist for more than 10 years

49766785

Type Your Microsoft Office Question Here...

Jess M. is online now

Im trying to create a vlookup function in excel that performs

This answer was rated:

★★★★★

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

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

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!

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".

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?

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.

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:

Excel will lookup the date in column T in the first sheet inside the second sheet.

When a match is found, take column E of the second sheet as the result.

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.

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.

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.

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.

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?

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.

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.

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.

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.

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?

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.

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.