Looking up data in a different worksheet based on criteria

[jeeperv6]

Hi,

New here, first post. I've been scratching my head over this one. I usually only use basic spreadsheet functions in Excel 2003, but due to business reasons I'm having to get a little bit more complicated.

Here's my scenario. I'm beginning to get into the import business of parts here in Canada. I've got a spreadsheet that adds my costs (exchange rate, shipping, etc) to the particular item. All the parts that I am importing have different values and as such are charged different brokerage fees.

ie:

Value Brokerage

100.00 25.00
250.00 30.50
500.00 55.00

I have all my brokerage fees in a worksheet called "Brokerage". My costs are in another worksheet named "Costs". What I would like to do is have Excel look up the value of the part and automatically enter in the brokerage fee in the "Costs" worksheet.

Something like:

Item Value Brokerage

Oil Cooler 499.00 55.00

If I could get Excel to do this for me, it would save me a great amount of time spent manually entering in the brokerage fees.

Thanks for any help or suggestions on how to go about this.

[ConneXionLost]

You could most likely do everything you need to with the VLOOKUP function. If you post a sample workbook, showing you would be easier.

[GaidenFocus]

so what i get from your post you want to type in the part and it automatically puts in the fees?

thats simple

in the column that you want to have the fees show up in just type the following

(lets say your part is in a1)

Please Login or Register  to view this content.

using this it will be a zero if you have not yet entered the part, and when you do, if it fins a match then it will give you the cost for the part number. adjust the cell references as neccisary, as im not sure where exactly you have your lists.

[jeeperv6]

You could most likely do everything you need to with the VLOOKUP function. If you post a sample workbook, showing you would be easier.

Here's most of the spreadsheet I have set up.

Brokerage contains all the brokerage fees I have to add to my costs.

Our Costs is the sheet that includes all my combined costs.

What I want to do is have Excel look up the value of an item, ie the Oil Cooler & put in the correct brokerage fee (93.50) into the the cell under the Broker(age) Fees column.

I don't mind looking up the correct brokerage fees on a small sheet (this is a small one for me), but on the larger sheets with 100 or more items it's a little bit tedious.

I've attached the sheet.

[ConneXionLost]

Mods made to "Our Costs" tab, as well as mods (in yellow) to the "Brokerage" tab.

Hope that helps.

Cheers,

[jeeperv6]

Mods made to "Our Costs" tab, as well as mods (in yellow) to the "Brokerage" tab.

Hope that helps.

Cheers,

Thank you, it helps alot. The one thing I didn't mention though and from what I've read is that Excel tends to go with the closest lower match if it doesn't find an exact match.

Can I get Excel to use the next highest level.

For example, instead of using 93.50, have it use 102.50?

Thank you again for getting me on the right track.

[ConneXionLost]

The easiest way to do that would be to shift your VFD column down one row; but please note I've done that once already for you in the example. If you do it again, how will you deal with your minimum VFD values?

[jeeperv6]

The easiest way to do that would be to shift your VFD column down one row; but please note I've done that once already for you in the example. If you do it again, how will you deal with your minimum VFD values?

Thanks for your help again!. And yes, that's my question exactly. As I said, the way the Brokerage firm does it is that even though the oil cooler is less than 2000.00, it's over 1500.00 so they charge me the 2000.00 brokerage fee.

I had originally tried to do this with IF statements, but Excel only allows 7 nested IF's.

[ConneXionLost]

So, looking at your Brokerage tab, your fee for a minimum VFD would be $30.50 dollars. What about the $25 fee?

[jeeperv6]

So, looking at your Brokerage tab, your fee for a minimum VFD would be $30.50 dollars. What about the $25 fee?

Ok. I went back and double checked everything. It does work as you had made the changes. I goofed and was looking at my original spreadsheet. I had copied in your formula for the Our Costs worksheet, but had forgotten to make the changes to the Brokerage worksheet. Once I did that, everything jives. Checked all the items manually and it is taking the next highest value (as exactly the Broker would do).

Thank you again for your time and patience!

Jeeperv6

[jeeperv6]

Gosh! It's been that many years (2010) since I first came here looking for help and found a great place to learn. Time flies.

The help and guidance provided by this forum has lasted me this many years. Times have changed and I am looking for help & suggestions on how to modify my original costing spreadsheet to reflect my current needs.

As well as doing phone sales/orders, I am finding myself bidding on more and more bid tenders for my products. While I have find tuned the look up solution provided by ConneXionLost, to auto round up the next highest Customs value, I have a few other ideas I would like to implement on this sheet to better serve my needs.

Basically,

1) The "Our Costs" tab - Is it possible to have the sheet ask me whether I want Brokerage pricing on individual or multiple items and automatically insert the correct formula in the cell. Some of my bid tenders are awarded individually and some are awarded on a whole.

2) The "Mark Up" tab - Is it possible to again have a pre-defined drop down box with pre-filled in percentages instead of scrolling across the entire page(s)?

I have deleted a few of the non-necessary tabs (part numbers, trailer space, local/national taxes) in this worksheet.

Any help or guidance in how/if this is possible is much appreciated.

[FDibbins]

I know this is your thread, and the question would appear to be an extension of your 1st question, but it might be better if you started a new thread with this question, and referenced this thread

[jeeperv6]

Hi FDibbins,

I had wondered about that. I wasn't sure if I should start a new thread or not as this is technically an extension of my original question.

But I think I will follow your suggestion. And make a new post referring to this post.

Thanks!

Jeeperv6