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

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

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