HLOOKUP cheapest value from multiple rows

**jamiegfinch** · 08-28-2014, 08:22 PM

Hi.
I'm trying to do an excel spreadsheet for my work that will show which supplier that we buy from is the cheapest. Where in sheet1 I have 2 combo boxes. ComboBox 1 in cell C4 has Screws/Bolts. In ComboBox 2 (D4), it has whatever screws or Bolts appears in that category. In Sheet 2 and 3 I have all the different types of screws/bolts listed in Column A and in every other column I have that particular screw/bolt as priced from each different supplier. For example, column B = 1st Suppliers price, Column C = Date (that it was priced), Column D = 2nd Suppliers price, Column E = Date (that it was priced), etc,etc. What I am trying to achieve is a formula to say IF the first ComboBox (C4) is "screws" then lookup ComboBox 2 (D4) as to what type of screw it is. And look at sheet 2 or 3 as to who might be the cheapest and return that Value (whilst ignoring the dates in every other column). How would I go about doing that?

**JBeaucaire** · 08-28-2014, 08:26 PM

At first blush this sounds doable, but we'd need to be working in your actual workbook to get the formula right.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

**jamiegfinch** · 08-28-2014, 08:52 PM

Okay I have attached a example workbook. Obviously I have stuffed around with the prices so they won't be accurate but the idea is still there. I'm hoping to be able to include the Supplier and the Date as well.Screws2.xlsm

**JBeaucaire** · 08-29-2014, 02:51 PM

Like so...

1) I removed the blank rows on your resource sheets.
2) I removed the hidden lists in column G, unneeded and require manual maintenance, we can do better.
3) Column A of each resource sheet is now used dynamically as the "source" for the drop downs in column B of the Main.
4) All resource sheets have 3 new columns added (B:D) that is collecting the cheapest price for you from each row, in advance.
If more than one supplier offers the lowest price, the FIRST vendor will be returned. So order your vendor by preference from left to right.

There is no VBA in this solution at all, we are using standard Data Validation drop downs and formulas.