Formula to return vendor names based on costs

[tparmbru22]

I need help creating a formula that will return the vendor name based on their cost. I need the formula to return the lowest 3 vendors. The formula will be used in COLUMNS B,C,D.

I've tried using the INDEX,MATCH function combo but I'm not having any luck. Below is an example of the data I'm using.

Any help would be appreciated.

	A	B	C	D	E	F	G	H	I
1	Destination	1st Low Bid	2nd Low Bid	3rd Low Bid	Vendor1	Vendor2	Vendor3	Vendor4	Vendor5
2	Destination1	Vendor4	Vendor2	Vendor3	25	15	20	10	50
3	Destination2	Vendor1	Vendor2	Vendor3	20	30	30		40
4	Destination3	Vendor2	Vendor3	Vendor1	30	25	25	40	45
5	Destination4	Vendor1	Vendor4	Vendor3	15		30	20	40
6	Destination5	Vendor1	Vendor3	Vendor2	10	20	15	30
7	Destination6	Vendor2	Vendor1	Vendor5	45	40	60	60	45
8	Destination7	Vendor1	Vendor2	Vendor5	50	55		70	60
9	Destination8	Vendor4	Vendor1	Vendor5	35	60	50	25	45
10	Destination9	Vendor3	Vendor1	Vendor4	20		10	30	40
11	Destination10	Vendor3	Vendor5	Vendor2		45	30		35

<tbody>
</tbody>

 

[Simon4s]

B1: =INDEX(E1:I1,MATCH(SMALL(E2:I2,1),E2:I2,0))
C1: =INDEX(E1:I1,MATCH(SMALL(E2:I2,2),E2:I2,0))
D1: =INDEX(E1:I1,MATCH(SMALL(E2:I2,3),E2:I2,0))
Copy down

 

[tparmbru22]

Thanks for the reply.

This only returns the value in COLUMNS E:I. I need the formula to return the vendor name, which is the column header and not the cost below.

 

[Simon4s]

Thanks for the reply.

This only returns the value in COLUMNS E:I. I need the formula to return the vendor name, which is the column header and not the cost below.

updated. absolute the index array btw.

 

[njimack]

Enter the following in B2, and then copy across to C2:D2, and down as far as required...
=INDEX($E$1:$I$1,MATCH(SMALL($E2:$I2,COLUMN(A2)),$E2:$I2,0))

 

[tparmbru22]

Enter the following in B2, and then copy across to C2:D2, and down as far as required...
=INDEX($E$1:$I$1,MATCH(SMALL($E2:$I2,COLUMN(A2)),$E2:$I2,0))

This works almost, except when their are ties. In ROW # 3, Vendor2 and Vendor3 have the same value of 30 but in COLUMNS C and D the formula returns only Vendor2 in each column. Is it possible to show Vendor3 in COLUMN D instead of Vendor2?

 

[tparmbru22]

B1: =INDEX(E1:I1,MATCH(SMALL(E2:I2,1),E2:I2,0))
C1: =INDEX(E1:I1,MATCH(SMALL(E2:I2,2),E2:I2,0))
D1: =INDEX(E1:I1,MATCH(SMALL(E2:I2,3),E2:I2,0))
Copy down

This formula has the same problem as the other, it won't return the next vendor if there are ties. ROW # 3, this formula returns Vendor2 in both COLUMNS C and D and I need it to show Vendor2 and Vendor3 instead.

 

[wideboydixon]

Enter the following in B2, and then copy across to C2:D2, and down as far as required...
=INDEX($E$1:$I$1,MATCH(SMALL($E2:$I2,COLUMN(A2)),$E2:$I2,0))

FWIW, this won't work for the duplicate values on row 3 for example; it will give you Vendor1, Vendor2, Vendor2. You need to ignore vendors already listed:

Book1
	A	B	C	D	E	F	G	H	I
1	Destination	1st Low Bid	2nd Low Bid	3rd Low Bid	Vendor1	Vendor2	Vendor3	Vendor4	Vendor5
2	Destination1	Vendor4	Vendor2	Vendor3	25	15	20	10	50
3	Destination2	Vendor1	Vendor2	Vendor3	20	30	30		40
4	Destination3	Vendor2	Vendor3	Vendor1	30	25	25	40	45
5	Destination4	Vendor1	Vendor4	Vendor3	15		30	20	40
6	Destination5	Vendor1	Vendor3	Vendor2	10	20	15	30
7	Destination6	Vendor2	Vendor1	Vendor5	45	40	60	60	45
8	Destination7	Vendor1	Vendor2	Vendor5	50	55		70	60
9	Destination8	Vendor4	Vendor1	Vendor5	35	60	50	25	45
10	Destination9	Vendor3	Vendor1	Vendor4	20		10	30	40
11	Destination10	Vendor3	Vendor5	Vendor2		45	30		35
Sheet1

Cell Formulas
Range	Formula
B2	{=INDEX($E$1:$I$1,MIN(IF($E2:$I2=SMALL($E2:$I2,COLUMN(A$1)),IF(COUNTIF($A2:A2,$E$1:$I$1)=0,COLUMN($E$2:$I$2)-COLUMN($E$2)+1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Copy B2 across and down.

WBD

 

[tparmbru22]

FWIW, this won't work for the duplicate values on row 3 for example; it will give you Vendor1, Vendor2, Vendor2. You need to ignore vendors already listed:

	A	B	C	D	E	F	G	H	I
1	Destination	1st Low Bid	2nd Low Bid	3rd Low Bid	Vendor1	Vendor2	Vendor3	Vendor4	Vendor5
2	Destination1	Vendor4	Vendor2	Vendor3	25	15	20	10	50
3	Destination2	Vendor1	Vendor2	Vendor3	20	30	30		40
4	Destination3	Vendor2	Vendor3	Vendor1	30	25	25	40	45
5	Destination4	Vendor1	Vendor4	Vendor3	15		30	20	40
6	Destination5	Vendor1	Vendor3	Vendor2	10	20	15	30
7	Destination6	Vendor2	Vendor1	Vendor5	45	40	60	60	45
8	Destination7	Vendor1	Vendor2	Vendor5	50	55		70	60
9	Destination8	Vendor4	Vendor1	Vendor5	35	60	50	25	45
10	Destination9	Vendor3	Vendor1	Vendor4	20		10	30	40
11	Destination10	Vendor3	Vendor5	Vendor2		45	30		35

<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula B2 {=INDEX($E$1:$I$1,MIN(IF($E2:$I2=SMALL($E2:$I2,COLUMN(A$1)),IF(COUNTIF($A2:A2,$E$1:$I$1)=0,COLUMN($E$2:$I$2)-COLUMN($E$2)+1))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Copy B2 across and down.

WBD

Thanks this worked perfectly and solved the duplicate value problem.

I have one more question. I applied the formula to my data and needed to insert 2 columns in front of "Destination". Destination is now in COLUMN C and by doing this, the same problem occurred as before where Vendor2 showed up twice instead of Vendor2 and Vendor3. How can I modify the formula to solve for that?

 

[wideboydixon]

Check the formula which might now show COLUMN(C$1) and change it back to COLUMN(A$1).

WBD