VLookup smallest amount and fill in last column with text

[LindaGibson]

I have a spread sheet listing types of paper, (4) vendors and their price for such. I want the last column to show who has the lowest price.

columns are:

paper / weight / Vendor 1 price / Vendor 2 price / Vendor 3 price / Vendor 4 price / Best price

I want a formula that will lookup and compare all 4 vendor's prices (columns C, D, E & F) and have their name be inserted in column G (as follows)

Vendor1 = JC
Vendor2 = UNI
Vendor3 = SP
Vendor4 = XPX

how would I do that?

Thanks so much!
Linda

 

[Zack Barresse]

Hi Linda, welcome to MrExcel!!

I think you'd probably get better results with an INDEX/MATCH combination. Here's an example...

Book6
	C	D	E	F	G
1	JC	UNI	SP	XPX	Best Price
2	87	13	30	16	UNI
3	6	48	83	51	JC
4	48	36	56	27	XPX
5	95	73	47	31	XPX
6	24	23	24	27	UNI
7	28	63	32	51	JC
8	5	7	45	59	JC
9	87	20	41	80	UNI
10	9	37	31	56	JC
11	39	16	56	48	UNI
12	26	92	61	87	JC
13	71	38	30	12	XPX
14	5	65	11	50	JC
Sheet1

formula is:

=INDEX($C$1:F2,1,MATCH(MIN($C2:$F2),$C2:$F2,0))

copy down as needed. This does require that you have their names in row 1. If not, it will be more difficult, but can be worked around.


edit: btw, a formula for that might look like this...

=CHOOSE(MATCH(INDEX($C$1:F2,1,MATCH(MIN($C2:$F2),$C2:$F2,0)),$C$1:$F$1,0),"JC","UNI","SP","XPX")

 

[BJungheim]

Re: VLookup smallest amount and fill in last column with tex

In G2:
=OFFSET($Z$1,MATCH(MIN($C2:$F2),$C2:$F2,-1),0)

With your company list starting at Z1 listed down.

 

[fairwinds]

Re: VLookup smallest amount and fill in last column with tex

Hi,

If there is a risk that more than one supplier has the lowest bid I would do like this:

Book1
	C	D	E	F	G
1	JC	UNI	SP	XPX	Best Price
2	87	13	30	16	UNI
3	6	6	48	48	JC UNI
4	48	27	27	27	UNI SP XPX
5	95	95	47	31	XPX
6	24	23	24	27	UNI
7	28	28	28	28	JC UNI SP XPX
8	5	7	45	59	JC
9	87	20	41	80	UNI
Sheet3

 

[Aladin Akyurek]

Re: VLookup smallest amount and fill in last column with tex

I have a spread sheet listing types of paper, (4) vendors and their price for such. I want the last column to show who has the lowest price.

columns are:

paper / weight / Vendor 1 price / Vendor 2 price / Vendor 3 price / Vendor 4 price / Best price

I want a formula that will lookup and compare all 4 vendor's prices (columns C, D, E & F) and have their name be inserted in column G (as follows)

Vendor1 = JC
Vendor2 = UNI
Vendor3 = SP
Vendor4 = XPX

how would I do that?

Thanks so much!
Linda

Using the formula system I described in:

http://www.mrexcel.com/board2/viewtopic.php?t=72823&start=10

aaBestPriceVendors LindaGibson.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	paper	weight	JC	UNI	SP	XPX	Rank						BestPrice
2	Letter	80	3.99	3.49	2.89	4.02	3	2	1	4	0	1	SP
3	A4	70	3.1	2.5	3.92	2.5	3	1	4	2	1	1	UNI	XPX
4	A3	90	1.81	1.81	2.91	2.64	1	2	4	3	1	1	JC	UNI
5	B5	80	3.77	3.77	4.8	3.4	2	3	4	1	0	1	XPX
6
Vendors

G2, copied across to J2 then down:

=RANK(C2,$C2:$F2,1)+COUNTIF($C2:C2,C2)-1

K2:

=MAX(IF(INDEX(C2:F2,MATCH($L2,G2:J2,0))=C2:F2,G2:J2))-$L2

which must be confirmed with control+shift+enter instead of just with enter and then copied down.

Computes the number of ties of the best price.

L2: 1, the Top N paramater, which is copied down.

M2, which is copied across to P2 then down:

=IF(COLUMN()-COLUMN($M2)+1<=$L2+$K2,INDEX($C$1:$F$1,MATCH(COLUMN()-COLUMN($M2)+1,$G2:$J2,0)),"")

 

[LindaGibson]

Re: VLookup smallest amount and fill in last column with tex

Thank you soooooo much! I knew you guys would come through!