Using if formula to find best vendor price

Stevereyn29

New Member
Joined
Mar 27, 2018
Messages
5
https://imgur.com/Dlb67mk

I have a spreadsheet with different companies and how much they charge for gas. I need to make an if formula that will put the company name with the cheapest of each gas type in the "Jet Fuel Vendor" and "AVgas Vendor". The exact wording of what I need to do is

Using nested “IF” functions, create a formula for the cell in Column B, Row 9 that places the name of the “winning” vendor for the “Best Jet Fuel Price.”

Using nested “IF” functions, create a formula for the cell in Column C. Row 12 that places the name of the “winning” vendor for the “Best Avgas Price.”

I figured out how to find the minimum of each row but I don't know how to make it pick the name of the cheapest one. I've been sitting here frusturated for an hour and half. It has to be an IF. Please help
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

Something like this?

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">ABC Fuel</td><td style="text-align: right;;">$5.00 </td><td style="text-align: right;;">$9.00 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Midwest Fuel</td><td style="text-align: right;;">$7.50 </td><td style="text-align: right;;">$6.45 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Texas Wildcat Fuel</td><td style="text-align: right;;">$5.25 </td><td style="text-align: right;;">$7.00 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">KC Fuel</td><td style="text-align: right;;">$6.15 </td><td style="text-align: right;;">$8.50 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Royal Canadian Fuel</td><td style="text-align: right;;">$8.00 </td><td style="text-align: right;;">$6.50 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Best Jet Fuel Price</td><td style="text-align: right;;">$5.00 </td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Jet Fuel Vendor</td><td style=";">ABC Fuel</td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Best Avgas Price</td><td style="text-align: right;;"></td><td style="text-align: right;;">$6.45 </td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Avgas Vendor</td><td style="text-align: right;;"></td><td style=";">Midwest Fuel</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B9</th><td style="text-align:left">=INDEX(<font color="Blue">$A$2:$A$6,MATCH(<font color="Red">MIN(<font color="Green">B$2:B$6</font>),B$2:B$6,0</font>)</font>)</td></tr></tbody></table></td></tr></table><br />

B9 formula copied to C12.
 

Stevereyn29

New Member
Joined
Mar 27, 2018
Messages
5
It is homework, but I don't see how it's possible to use nested IF to do what we're supposed to do. I've never used excel in my life and all we were given for "instructions" was this useless video that doesn't pertain to our assignment. I just don't get it. https://www.youtube.com/watch?v=MtDGUHdS3VY
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,307
Perhaps this example will help, but not the most efficient way.
Excel Workbook
AB
1a5
2b6
3c4
4
5
6Min
7
Sheet
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
51,128
Office Version
365
Platform
Windows
It is homework, but I don't see how it's possible to use nested IF to do what we're supposed to do. I've never used excel in my life
What kind of course is this? Is it an Excel class or some kind of business class?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
AhoyNC has given you an example of what a nested IF formula may look like, I also suggest searching our forum for "Nested IF" and/or the WWW for more samples so that you can try and solve your query.

If you put together a formula, but can not get it work correctly, post back with what you have, then someone can help point you in the right direction to correct it.

Good Luck.
 

Stevereyn29

New Member
Joined
Mar 27, 2018
Messages
5
What kind of course is this? Is it an Excel class or some kind of business class?
Random business class. I don't know anything about Excel. Unfortunately these 2 IF formulas are 40% of the grade for this assignment.

What I was trying to write is =if(b2:b6=b8,"name of company in A column). I know that isn't an actual formula but do you see what I'm getting at? I am trying to make it say the name of the company from Column A who's B value matches the calculated min in B8.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Random business class. I don't know anything about Excel. Unfortunately these 2 IF formulas are 40% of the grade for this assignment.

What I was trying to write is =if(b2:b6=b8,"name of company in A column). I know that isn't an actual formula but do you see what I'm getting at? I am trying to make it say the name of the company from Column A who's B value matches the calculated min in B8.
I strongly suggest you study the formula AhoyNC posted above, figure out his logic based on his sample, and try to adapt it to your question.
 

Forum statistics

Threads
1,077,664
Messages
5,335,566
Members
399,025
Latest member
alce

Some videos you may like

This Week's Hot Topics

Top