Finding the name of the two People who have a tie as least cost

[JDMAS]

Hey everyone,

This is my issue. I have a project where I have to find who provided the minimum cost. When there are two people who tie for minimum cost, I have to show both people on a spreadsheet. Normally, I would just search for the number and manually write it in, but it is getting to the point where I have 60+ people providing costs going 2000 rows down!

I have tried to come up with a formula by using index and offsets, but I cant seem to find the right combination.

My layout on the spreadsheet looks like this:


[TABLE="width: 1187"]
<tbody>[TR]
[TD]1st least cost[/TD]
[TD]1st least cost name[/TD]
[TD]2nd least cost[/TD]
[TD]2nd least cost name[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Jake[/TD]
[TD]Jim[/TD]
[TD]Al[/TD]
[TD]Mr. White[/TD]
[TD]Jessi[/TD]
[TD]Andi[/TD]
[TD]Carol[/TD]
[/TR]
[TR]
[TD="align: right"]$1.00[/TD]
[TD]Al[/TD]
[TD="align: right"]$1.00[/TD]
[TD]Al[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$44.00[/TD]
[TD="align: right"]$11.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]$87.00[/TD]
[TD="align: right"]$1.00[/TD]
[TD="align: right"]$45.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: center"]#VALUE![/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]$10.00[/TD]
[TD]Jake[/TD]
[TD="align: right"]$17.00[/TD]
[TD]Jim[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]$17.00[/TD]
[TD="align: right"]$65.00[/TD]
[TD="align: right"]$58.00[/TD]
[TD="align: right"]$46.00[/TD]
[TD="align: right"]$59.00[/TD]
[TD="align: right"]$77.00[/TD]
[/TR]
[TR]
[TD="align: right"]$4.00[/TD]
[TD]Mr. White[/TD]
[TD="align: right"]$10.00[/TD]
[TD]Carol[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$72.00[/TD]
[TD="align: right"]$37.00[/TD]
[TD="align: right"]$78.00[/TD]
[TD="align: right"]$4.00[/TD]
[TD="align: right"]$97.00[/TD]
[TD="align: right"]$82.00[/TD]
[TD="align: right"]$10.00[/TD]
[/TR]
[TR]
[TD="align: right"]$6.00[/TD]
[TD]Jim[/TD]
[TD="align: right"]$8.00[/TD]
[TD]Al[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$97.00[/TD]
[TD="align: right"]$6.00[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$8.00[/TD]
[TD="align: right"]$94.00[/TD]
[TD="align: right"]$9.00[/TD]
[/TR]
[TR]
[TD="align: right"]$2.00[/TD]
[TD]Mr. White[/TD]
[TD="align: right"]$10.00[/TD]
[TD]Al[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$50.00[/TD]
[TD="align: right"]$56.00[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]$2.00[/TD]
[TD="align: right"]$86.00[/TD]
[TD="align: right"]$25.00[/TD]
[TD="align: right"]$39.00[/TD]
[/TR]
[TR]
[TD="align: right"]$20.00[/TD]
[TD]Jessi[/TD]
[TD="align: right"]$40.00[/TD]
[TD]Mr. White[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$86.00[/TD]
[TD="align: right"]$75.00[/TD]
[TD="align: right"]$48.00[/TD]
[TD="align: right"]$40.00[/TD]
[TD="align: right"]$20.00[/TD]
[TD="align: right"]$71.00[/TD]
[TD="align: right"]$98.00[/TD]
[/TR]
[TR]
[TD="align: right"]$24.00[/TD]
[TD]Mr. White[/TD]
[TD="align: right"]$24.00[/TD]
[TD]Mr. White[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$74.00[/TD]
[TD="align: right"]$34.00[/TD]
[TD="align: right"]$74.00[/TD]
[TD="align: right"]$24.00[/TD]
[TD="align: right"]$45.00[/TD]
[TD="align: right"]$24.00[/TD]
[TD="align: right"]$24.00[/TD]
[/TR]
[TR]
[TD="align: right"]$37.00[/TD]
[TD]Jake[/TD]
[TD="align: right"]$37.00[/TD]
[TD]Jake[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]$37.00[/TD]
[TD="align: right"]$37.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$74.00[/TD]
[TD="align: right"]$100.00[/TD]
[TD="align: right"]$53.00[/TD]
[TD="align: right"]$38.00[/TD]
[/TR]
</tbody>[/TABLE]


The #VALUE! is where I need to show who else provided the least cost.

The formula I wrote (which does not work) is: =INDEX($J$2:$P$11,1,OFFSET(J2,,MATCH(A3,J3:P3,0)))

I was trying to write a formula where it finds the first min cost, ignore it, and find the other min cost. I believe I am on the right track, but I cant seem to get it to work.

Any help would be greatly appreciated!!!

 

[AhoyNC]

This seems to work if tie for first, but if a tie for second only finds 1st (see row 5).

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	1st least cost	Name	2nd least cost	Name			Jake	Jim	Al	Mr. White	Jessi	Andi	Carol
2	1	Al	1	Andi			$44.00	$11.00	$1.00	$2.00	$87.00	$1.00	$45.00
3	10	Jake	17	Jim			$10.00	$17.00	$65.00	$58.00	$46.00	$59.00	$77.00
4	4	Mr. White	10	Carol			$72.00	$37.00	$78.00	$4.00	$97.00	$82.00	$10.00
5	6	Jim	8	Al			$97.00	$6.00	$8.00	$20.00	$8.00	$94.00	$9.00
6	2	Mr. White	10	Al			$50.00	$56.00	$10.00	$2.00	$86.00	$25.00	$39.00
7	20	Jessi	40	Mr. White			$86.00	$75.00	$48.00	$40.00	$20.00	$71.00	$98.00
8	24	Mr. White	24	Andi			$74.00	$34.00	$74.00	$24.00	$45.00	$24.00	$24.00
9	37	Jake	37	Jim			$37.00	$37.00	$100.00	$74.00	$100.00	$53.00	$38.00
Sheet

 

[JDMAS]

This is perfect! Thank you so much! You have saved me countless hours!!!!

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.