Looking up data in a different worksheet based on criteria

Originally Posted by 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.

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.

Originally Posted by ConneXionLost

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.

Originally Posted by 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?

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.

Originally Posted by ConneXionLost

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

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.

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