Using if formula to find best vendor price

[Stevereyn29]

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]

Hi,

Something like this?

Book1
	A	B	C
2	ABC Fuel	$5.00	$9.00
3	Midwest Fuel	$7.50	$6.45
4	Texas Wildcat Fuel	$5.25	$7.00
5	KC Fuel	$6.15	$8.50
6	Royal Canadian Fuel	$8.00	$6.50
7
8	Best Jet Fuel Price	$5.00
9	Jet Fuel Vendor	ABC Fuel
10
11	Best Avgas Price		$6.45
12	Avgas Vendor		Midwest Fuel
Sheet1

Cell Formulas
Range	Formula
B9	=INDEX($A$2:$A$6,MATCH(MIN(B$2:B$6),B$2:B$6,0))

B9 formula copied to C12.

 

[Stevereyn29]

Is that considered a nested IF function though?

 

[jtakw]

Is that considered a nested IF function though?

No, a nested IF is not the way to go for this.

If you MUST use a nested IF, that's a hint it's some kind of homework, then I can't help you.

 

[Stevereyn29]

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]

Perhaps this example will help, but not the most efficient way.

Excel Workbook
	A	B
1	a	5
2	b	6
3	c	4
4
5
6	Min	4
7		c
Sheet

 

[Joe4]

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]

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]

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]

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.