Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup query in excel
Hi,
I am trying to return the column heading name for a formulated set of results. How do I do this? I have tried HLOOKUP but this only works when the column heading is pasted directly below the cells I am looking up. I have many rows and this results in an error message when I try to use it My dataset contains the following: "Product name" in the left most column, with the column headings going across the page for each sales competitor. I have used a MIN function to extract the lowest competitor price (to exclude Zero for other products). This is returned in the right most column. Using this formulated result (in the lowest price column, I now need to derive who the competitor is for that particular returned value (e.g. X,Y or Z. Can anyone help with this? It would be greatly appreciated Product name comp X comp Y comp Z Lowest price Lowest Comp abcde 100.0 101.00 102.00 100 Regards Lee |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup query in excel
Try:
=INDEX($B$1:$D$1,1,MATCH(MIN($B2:$D2),$B2:$D2,0)) HTH "LM" wrote: Hi, I am trying to return the column heading name for a formulated set of results. How do I do this? I have tried HLOOKUP but this only works when the column heading is pasted directly below the cells I am looking up. I have many rows and this results in an error message when I try to use it My dataset contains the following: "Product name" in the left most column, with the column headings going across the page for each sales competitor. I have used a MIN function to extract the lowest competitor price (to exclude Zero for other products). This is returned in the right most column. Using this formulated result (in the lowest price column, I now need to derive who the competitor is for that particular returned value (e.g. X,Y or Z. Can anyone help with this? It would be greatly appreciated Product name comp X comp Y comp Z Lowest price Lowest Comp abcde 100.0 101.00 102.00 100 Regards Lee |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Lookup query in excel
To take ties into consideration, assuming that A1:E2 contains the data,
try the following formula which needs to confirmed with CONTROL+SHIFT+ENTER... F2, copied across and down, if necessary: =IF(COLUMNS($F2:F2)<=COUNTIF($B2:$D2,$E2),INDEX($B $1:$D$1,SMALL(IF($B2:$D 2=$E2,COLUMN($B2:$D2)-COLUMN($B2)+1),COLUMNS($F2:F2))),"") Hope this helps! In article om, "LM" wrote: Hi, I am trying to return the column heading name for a formulated set of results. How do I do this? I have tried HLOOKUP but this only works when the column heading is pasted directly below the cells I am looking up. I have many rows and this results in an error message when I try to use it My dataset contains the following: "Product name" in the left most column, with the column headings going across the page for each sales competitor. I have used a MIN function to extract the lowest competitor price (to exclude Zero for other products). This is returned in the right most column. Using this formulated result (in the lowest price column, I now need to derive who the competitor is for that particular returned value (e.g. X,Y or Z. Can anyone help with this? It would be greatly appreciated Product name comp X comp Y comp Z Lowest price Lowest Comp abcde 100.0 101.00 102.00 100 Regards Lee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
stock quote lookup query for Excel 2000 | Excel Worksheet Functions | |||
Get External Data - not editable using Query Wizard | Excel Discussion (Misc queries) | |||
Import query from access to excel, link to template, email on | Links and Linking in Excel | |||
Excel 03 DB query pulls data in wrong order, how to resolve? | Excel Discussion (Misc queries) | |||
Error message in Excel after exporting Access query to Excel | Excel Discussion (Misc queries) |