I have a list of Vendors and pricing. When I add a delivery, I want to look up the cost for that item. The problem is, I have chemicals coming from more than one vendor throughout the course of the year (not at the same time, though)..

Here's an example of the vendors price list and contract dates:
Chemical Vendor Cost Contract Start Contract Stop
Chem1 Vendor1 83.48 1/1/2005 10/31/2005
Chem1 Vendor2 85.48 11/1/2005 11/30/2006
Chem2 Vendor3 86.3 1/1/2005 12/31/2005
chem3 vendor4 150 1/1/2005 12/31/2005


My delivery data is as follows:
10/26/05 Chem1

I want to look up the vendor based on the chemical and delivery date. This should return 83.48 for the cost.

Is it possible to do this using the vlookup function?

ANY suggestions would be appreciated!
Thanks in advance,
Krista

BTW - I'm an Access person, not an Excel person. I could have completed this 20 times already in the amount of time I've spent trying to figure this out here. Unfortunately, my company likes things done in Excel.

The vLookup function by its self will not work for this. It takes a lookup value finds a match in one column and returns a value in x column on the same row.

I like to use the built in Filter tools for things like this, Auto Filter, Custom Filters. You can find these in the 'Data' Menu. For quick access I add the 'Auto filter' button and the 'Auto Filter' Toggle button to the main toolbar next to the sort buttons.

To get these buttons: (1) right-click on a toolbar and select 'Customize' (2) select the 'commands' tab, 'Data' menu list and drag the 'Funnel' Icon to the toolbar. (3) Now select your 'Data' dropdown menu from the main menu and drag the "AutoFilter" selection from the Data Filter Menu while holding the control key to copy it to your toolbar. (3) close the customise dialog window.

To use these select a cell in your data, click the funnel button to Auto Filter, click the 'AutoFilter' text button to release a filter or toggle the filter selection controls on your table. With the auto-filter mode on you can choose custom criteria or multi criteria selections in several columns by using the filter controls under the table headings.

This works like Access filters only quicker. I attached a clip of my toolbar section with the custom filter controls on it. Having these on the toolbar makes using the filters even easier. The other image shows the auto filter on with the criteria dropdown control open.

Still No... for the vLookup, but I just remembered another trick. If you want a formula solution then an Array Formula is the way go with this. Not many people know about array formulas. To make an Array Formula you must type the formula then use ctrl+shift+Enter key to finish editing the formula. The curly brackets are applied when you finish the formula with Ctrl+Shft+Edit.

Here's the Array Formula for your sample data. I'm referencing the criteria for the 'Chemical' name in G2 and the 'Date' in Cell G3. The result is displayed in the cell with the Formula in cell G5.

Good advice, Bill. The only concern with array formulas is that given a large number, it can slow the system down.

Another work-around might be to concatenate the columns and then do an INDEX/MATCH (more robust form of LOOKUP) based on that. I have found certain circumstances that this is the preferred solution.

I have around 15 vendors, but several deliveries arrive daily.. Which route would you recommend?

Oh, and I need to create a report, or pivot table, of deliveries by vendor by month. Any thoughts on how to do this?

For 15 Vendors either would work the Array formula or Index/Match on concatenated fields. To get a little fancier you can add a dropdown list control with an index to your cells for criteria used in the formula. Find the list controls on the 'Forms' Toolbar and open the controls Properties to set range for list values and cell to show selection.

As for Deliveries: You may want to maintain delivery records on a separate worksheet or even in a separate book. A unique Company ID or product ID will be useful to refer Products/companies to delivery records.

The Pivot Table is fairly easy to setup using the Pivot Table wizard. Once you have it configured you can use the Refresh button to revise the Pivot Table based on the latest data, as long as the references to the location of the data range does not change. The refresh button will be on the Pivot Table Toolbar.

OK - here's what I came up with, and it's working.

I gave each vendor a unique ID (from our purchasing system), and had the formula array calculate the cost, and in another column had a formula array return the vendor ID. I then did a vlookup based upon the vendor ID to get the vendor name. Schwew! Now I can create my pivot table based on vendors by month.

Thanks for the help!
Krista

BTW - I'm an Access person, not an Excel person. I could have completed this 20 times already in the amount of time I've spent trying to figure this out here. Unfortunately, my company likes things done in Excel.

Hi Krista,

Like you I was also in that situation. Knew Access would be better but the company wanted Excel. Though I was the one using the programs and nobody else I continued in Excel, and later developed it in Access. For a while I pull it from Access and dumped in Excel for them to see when need be. One day they came to me to get a quick report on something. In Excel that report took 13 steps to get what they wanted. In Access it took two clicks. They wondered when they would have the reports and I told them it was already on the network printer. They wondered HOW? I showed them and they were amazed. From then on they allowed ME to work how I saw best. Only one person still wanted to view in Excel for some reason, even though he couldn't really use it. I trained them in my custom database in a few minutes and they were happy ever after. Let them know without a lot of locking cells and all that stuff data and special info in cells can be deleted with entering that cell and hitting Delete, Space or whatever. You can lock it all but it a bit of a chore to me. Bring them around to Access slowly, and it will be better for you. If only they knew how much time would have been saved if you had been allowed to do in Access. Hang in there.

have a nice one,
BUD (in the Access section of this forum)

When I developed the base data, I gave it a dynamic named range, which automatically adjusted whent he PivotTable refreshed (regardless of # of rows or # of columns).

You could name it: myRng and then use the following formula to refer to the data:

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNT A(Sheet1!$1:$1))

We have five people adding data to the myRng every day. And they need different reports. Each has his/her own PivotTable, and all are based on this dynamic range. I have not had to touch this in over a month. Works great.