Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Return lowest value in a row and the corresponding title from the header

0

 Good morning, I have a table that shows average pricing by vendor for ~ 6,000 sku's (Table 1 in attached file) and a seperate table that will identify sku's with purchasing requirements (Table 2 in the attached file).  I'm trying to find the formula (hopefully not a VBA code), if possible, to return the lowest cost for each sku (lowest in column H and 2nd lowest in column J) along with the vendor for each of those cost (columns I and K). I greatly appreciate any advice you can offer on this.  Thank you.  

Answer
Discuss

Answers

0
Selected Answer

I set up 3 named ranges as follows.

  1. SKU = A3:A12
  2. Prices = B3:E12
  3. Vendors = B2:E2

I use these names in my formulas below. You can replace the names with their actual addresses (use absolute addressing!) in the formulas or you can set up the same names in your workbook but make the addresses dynamic so that the ranges adjust automatically as you add or delete data rows or columns and you never need to change your formulas again.

First, here is the formula to be pasted to H3 in your sample, and copied down from there. If you paste it to other locations note that 3 indicates the row to which the formula must be pasted and G is the column in which the SKU number is found.

=SMALL(INDEX(Prices,MATCH($G3,SKU,0),0),1)

SMALL(blah,blah,blah, 1) returns the lowest price. Therefore SMALL(blah,blah,blah, 2) returns the second-lowest. Therefore the formula below goes into J3.

=SMALL(INDEX(Prices,MATCH($G3,SKU,0),0),2)

The third formula goes into I3. It extracts the vendor name from the Vendors range based on the price found. Copy it from there to K3 and down in both columns as far as required.

=INDEX(Vendors,MATCH(H3,INDEX(Prices,MATCH($G3,SKU,0),0),0))

This formula will give the wrong result if both the lowest and second-lowest prices are the same. Since it finds the vendor by looking at the price it can only find the first vendor if both prices are identical.

The cure for this discrepancy looks more severe than the fault. You would have to say something like "IF the vendor just determined is different from the vendor in column I, determine the vendor in the way you just did, Else determine the second vendor with the same price".  The above formula becomes the test criterium and will have to be repeated to furnish the result if no action is needed. That doubles the size of formula plus a little extra for the IF() and reference ot column I, like =IF(A1 > 10, A1, 10).

Append to that a new formula to determine the second vendor. That formula would embed the same formula in a SMALL() function as explained here. This needs to be an array formula so that it can loop through the solutions it creates. The entire thing therefore would be about 4 times the size of the above and of array type.

Tell your preferred supplier to lower his price by a cent lol:

Discuss

Discussion

Thank you,
The formulas are working in my live data exactly as I'd hoped they would, and while the formulas made perfect sense as I copied them over, I would not have come up with this answer in my lifetime.  Can't thank you enough. 

Cheers!  
  
ejfolm (rep: 2) Apr 2, '20 at 2:59 pm
Thank you for the feedback and your enthusiastic words. I'm glad you got your problem solved. The custom here is to mark a good answer as "Selected" as a guide to other seekers of truth with similar a similar problem who might follow in your footsteps. They wouldn't waste their time reading something that isn't marked.
Variatus (rep: 4879) Apr 2, '20 at 8:24 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login