Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.newusers
 
Posts: n/a
Default Return Title to matched column

I have a spreedsheet of data. Bsically it contains various products and
various suppliers. The products are listed down the left side and the
suppliers along the top row. There is obviously more than one price per
product ie from each individual supplier. I have set up a basic min function
to return the cheapest price and a VERY complicated IF set of formulas to
return the suppliers names at present. However my biggest problem at
theminute is that i have more than 7 suppliesr and wish to simply get the
minimum number in the row to be represented by the cheapest suppliers name in
a set column. Hope you can help . . . I think it may be the HLookup that i
need ot use but i am having trouble
  #2   Report Post  
Posted to microsoft.public.excel.newusers
Max
 
Posts: n/a
Default Return Title to matched column

Assuming data is in cols A to K, data from row2 down, with products listed
in col A and suppliers' quotes in cols B to K (10 suppliers, say)

Put in L2, copy down:
=INDEX($B$1:$K$1,MATCH(MIN(B2:K2),B2:K2,0))

Note that in the event of any ties in the lowest prices,
only the leftmost lowest price supplier will be extracted

Sample illustration at:
http://cjoint.com/?cvhf6Mrxy5
Extract Lowest Price Supplier per Prod_matt_newusers.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
" wrote in
message ...
I have a spreedsheet of data. Bsically it contains various products and
various suppliers. The products are listed down the left side and the
suppliers along the top row. There is obviously more than one price per
product ie from each individual supplier. I have set up a basic min

function
to return the cheapest price and a VERY complicated IF set of formulas to
return the suppliers names at present. However my biggest problem at
theminute is that i have more than 7 suppliesr and wish to simply get the
minimum number in the row to be represented by the cheapest suppliers name

in
a set column. Hope you can help . . . I think it may be the HLookup that i
need ot use but i am having trouble



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Need to Improve Code Copying/Pasting Between Workbooks David Excel Discussion (Misc queries) 1 January 6th 06 04:56 AM
Lookup values in one column to return another [email protected] Excel Worksheet Functions 4 November 17th 05 10:42 PM
IF/AND/OR/DATEIF Issue...sorry...long post... EDSTAFF Excel Worksheet Functions 1 November 10th 05 01:28 AM
match and count words David Excel Worksheet Functions 5 July 4th 05 02:24 AM
Search one column and return value from next column shwekhaw Excel Discussion (Misc queries) 2 May 3rd 05 09:52 AM


All times are GMT +1. The time now is 09:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"