Jan 31 2019 01:40 AM
Jan 31 2019 01:40 AM
Apologize, Im not fluent in excel vocabulary yet. please, bare with me.
Im trying to find the "lowest bid" in a divisional formula for B2 through F3
And then have the =INDEX of the vendor name (ex: Vendor #1, Vendor #2, etc) show in column I (Vendor Name Column)
I'm Looking for a formula for Price and weight per ounce. The Lowest price per ounce Vendor Name enters into Column I "Vendor Name".
=> example: if the same product costs $17 for 18oz from Vendor #1, and 6.99 for 12 oz. from Vendor #2,
What formula to have the price per ounce EQUAL the lowest bid. (where vendor #2 is cheaper than Vendor #1 per ounce)
Lowest price per ounce = lowest bid.
Where the B2/B3 is the price per oz.
I need to have the "Vendor Name" column recognize this divisional formula and =INDEX the name
What I have in the "Low Bid' Column H only equals B2/B3 and not the entire B2/B3 : F3 comparison
Column H Formula : =MIN(B2:F3, B2/B3)
-( which gives only B2:B3 as answer for "low Bid" per ounce.)
Column I Formula : =INDEX($B$1:$F$1,MATCH(H2,B2:F2,0))
Note: only columns B2:B3 have this formula which equals the #N/A for I2.
If this isn't enough information, I apologize, please ask and I'm happy to share
heres the example:
A B C D E
Vendor / Item | Vendor #1 | Vendor #2 | Vendor #3 | Vedor #4 | Vendor #5 | Low Bid | Vendor Name | |
Product #1 | 17.04 | 6.99 | 53.65 | 0.946666667 | #N/A | |||
WEIGHT | 18 | 12.3 | 80 | 12.3 | Vendor #2 | |||
Product #2 | 19.82 | 6.31 | 6.31 | Vendor #5 | ||||
WEIGHT | 16 | 16 | 16 | Venor #1 |
Jan 31 2019 02:23 AM
SolutionHi,
That could be
=AGGREGATE(15,6,$B2:$F2/$B3:$F3,1)
for Low Bid
and
=LOOKUP($H2,$B2:$F2/$B3:$F3,$B$1:$F$1)
for Vendor name. Also in attached file.
Jan 31 2019 04:14 PM
Thank You Mr. Sergei, works perfectly.
Jan 31 2019 02:23 AM
SolutionHi,
That could be
=AGGREGATE(15,6,$B2:$F2/$B3:$F3,1)
for Low Bid
and
=LOOKUP($H2,$B2:$F2/$B3:$F3,$B$1:$F$1)
for Vendor name. Also in attached file.