Retrieving cell information after MIN()

[JackHenry]

I have a spreadsheet that is to be used to compare the price of the same item from several different companies after converting the price to Australian Dollars. Each company price is in a different countries dollar (GBP, USD AUD etc) and each company’s S/n (serial number) for the same part may be different.

I have a column for S/n, Quantity, Country$ price, and the converted AUD price for each company. These extend across several rows (company 1, company 2, etc)

EG
Company 1, S/n, Qty, Price in GBP, Price in $AUD Company 2, S/n, Qty, Price in $USD, Price in $AUD Etc (although the company name is only at the top of each group of columns) There may be dozens of rows of parts down the sheet

In a separate column, I then use the worksheet MIN() function to get the cheapest $AUD price for each part.

What I want to achieve is to have another 2 columns following the cheapest price that display the cheapest company and that companies S/n for that part.

Any assistance would be appreciated.

 

[Richard Schollar]

Hi JackHenry

Welcome to the Board!

It would make it much easier for viewers to understand your data structure if you could upload a sample of your data to the board (using Colo's html maker - see link in my signature). This basically gives an html representation of your spreadsheet. Also, if you could provide what you have and the result your after, then that would be especially useful.

There is a testing section of these forums where you can give the html maker a go (having installed it on your PC).

Best regards

Richard

 

[JackHenry]

OK, it seems a bit bunched up but you get the gist. I have 3 companies with the same part but different S/n's & prices. Each price gets converted to $AUD and the MIN is displayed in column M. Column N & O are what I'm after.. These display the company that had the best price and the corrosponding S/n from that company.

COPY - Elan requirements Suppliers prices.xls
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Part	Qty	Company1	GBP	AUD	Company2	GBP	AUD	Company3	USD	AUD		BestPrice	Company	S/n
2	Widget	2	S/nABC123	3.50	$16.10	S/NDEF123	4.75	$21.85	S/n123XYZ	USD3.60	$9.00		$9.00	Company3	S/n123XYZ
3	Thingy	3	S/nABC345	1.80	$12.42	S/NDEF345	2.36	$16.28	S/n345XYZ	USD4.20	$15.75		$12.42	Company1	S/nABC345
Sheet2

 

[fairwinds]

Hi,

Try:

N2: =VLOOKUP(M2,CHOOSE({1,2;3,4;5,6},E2,$C$1,H2,$F$1,K2,$I$1),2,0)
O2: =VLOOKUP(M2,CHOOSE({1,2;3,4;5,6},E2,C2,H2,F2,K2,I2),2,0)

both dragged down.

If the lowest price is a tie, only first ocurance will be shown.

 

[JackHenry]

Hi FairWinds

That works a treat. What would the correct syntax be if I had the information to lookup on another sheet in the same workbook. By this I mean, I would like to create a seperate sheet as a 'shopping list' without all of the other information on it. Just the best price , company and S/n.

Also, is it possible to colour the cells that are the lowest price in the main information area? In this case K2 & E3?

 

[fairwinds]

1.)

Just add the heet reference into the formula such as:
=VLOOKUP(M2,CHOOSE({1,2;3,4;5,6},Sheet1!E2,Sheet1!C2,Sheet1!H2,Sheet1!F2,Sheet1!K2,Sheet1!I2),2,0)


2.)
Use conditional formatting on the price cells with the "Formula is" option and the formula:
=E2=MIN(E2,H2,K2)

 

[JackHenry]

Excellent work.

Now, when I've pasted the formula down on Sheet2 and where a cell on Sheet 1 has no information yet, Sheet 2 displays #N/A. I know there is a way to leave the cell blank if that occurs, but my brain is getting old and you seem so useful as a resource.

 

[fairwinds]

I guess in that case youe MIN formula should show zero, thus:

=IF(M2,VLOOKUP(M2,CHOOSE({1,2;3,4;5,6},Sheet1!E2,Sheet1!C2,Sheet1!H2,Sheet1!F2,Sheet1!K2,Sheet1!I2),2,0),"")