Search and return Question

[Bogle]

Hi I am currently trying to horizontally look up a value and then return the heading of teh column that value is situtated in. I have tried the Hlookup functiona dn this does njot seem to work on its own. Alternatively I have written (what I thought was a basic If statement but that is having issues as well.

=if(ET2=DV2,$DV$1,if(ET2=DX2,$DX$1,if(ET2=DZ2,$DZ$1,if(ET2=EB2,$EB$1,if(ET2=ED2,$ED$1,if(ET2=EF2,$EF$1,if(ET2=EH2,$EH$1,if(ET2=EJ2,$EJ$1,if(ET2=EL2,$EL$1,if(ET2=EN2,$EN$1,if(ET2=EP2,$EP$1,if(ET2=ER2,$ER$1,""))))))))))))

Can anyone help?

 

[sanrv1f]

Hello and welcome to the board,

Not sure how your data look like, may be this

=INDEX(DV$1:ER$1,MATCH(ET2,DV2:ER2,0))

 

[rs2k]

I think you can only have 7 nested 'IF' statements.

is ET2 unique, or is this formula going into a lot of cells?

 

[Bogle]

It will be going into a lot of cells.....fill down over 3,000 cells

Sanrv1f you are very close I thinik. However my Data set is lated out like: Gp 1, Rev1, Gp 2, Rev 2, GP 3, REV 3
so i only want to match ET2 (which referes to gp) with those numbers in GP!, GP 2 and GP 3

I hope that makes sence.

Thanks for your help

 

[sanrv1f]

may be this,

=INDEX(DV$1:ER$1,MATCH(ET2,INDEX(IF(ISNUMBER(SEARCH("GP",DV2:ER2)),DV2:ER2),0),0))

If that didn't help, post some sample data and expected results

 

[Bogle]

Thanks again for the help.

Here is the format of my data
<TABLE style="WIDTH: 1043pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1390 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 166pt; mso-width-source: userset; mso-width-alt: 8082" width=221><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" width=200><COL style="WIDTH: 181pt; mso-width-source: userset; mso-width-alt: 8813" width=241><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7424" width=203><COL style="WIDTH: 181pt; mso-width-source: userset; mso-width-alt: 8813" width=241><COL style="WIDTH: 165pt; mso-width-source: userset; mso-width-alt: 8045" width=220><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: gray" width=64 height=17>Row</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 166pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=221>Product A - LineitemA Gross Profit</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 150pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=200>Product A - LineitemA Revenue</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 181pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=241>Product A - LineitemB Gross Profit</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 152pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=203>Product A - LineitemB Revenue</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 181pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=241>Product A - Lineitem Other Gross Profit</TD><TD class=xl25 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black; WIDTH: 165pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #ffcc00" width=220>Product A - Lineitem Other Revenue</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: gray" align=right height=17 x:num>1</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.05">5%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.08">8%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.06">6%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.04">4%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.07">7%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.05">5%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: gray" align=right height=17 x:num>2</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.04">4%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.04">4%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.05">5%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.05">5%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.04">4%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.05">5%</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl27 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: gray" align=right height=17 x:num>3</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.02">2%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.06">6%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.03">3%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.01">1%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.04">4%</TD><TD class=xl26 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.06">6%</TD></TR></TBODY></TABLE>
Unfortunately thay all have unique headings so the inbedded search did not quite work.

Here is the output I am trying to achieve.
<TABLE style="WIDTH: 697pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=929 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 166pt; mso-width-source: userset; mso-width-alt: 8082" width=221><COL style="WIDTH: 150pt; mso-width-source: userset; mso-width-alt: 7314" width=200><COL style="WIDTH: 181pt; mso-width-source: userset; mso-width-alt: 8813" width=241><COL style="WIDTH: 152pt; mso-width-source: userset; mso-width-alt: 7424" width=203><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: gray" width=64 height=17> </TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: black 0.5pt solid; WIDTH: 166pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffff" width=221>Maximum GP</TD><TD class=xl26 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: black; WIDTH: 150pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: #ccffff" width=200>Maximum Rev</TD><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: black; WIDTH: 181pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: gray" width=241>Maximum Act GP</TD><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #d4d0c8; BORDER-LEFT: black; WIDTH: 152pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: gray" width=203>Max Act Rev</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: gray" align=right height=17 x:num>1</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.07">7%</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.08">8%</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Product A - Lineitem Other Gross Profit</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Product A - LineitemA Revenue</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: gray" align=right height=17 x:num>2</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.05">5%</TD><TD class=xl28 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.05">5%</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Product A - LineitemB Gross Profit</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Product A - LineitemB Revenue</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl29 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: gray" align=right height=17 x:num>3</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.04">4%</TD><TD class=xl25 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right x:num="0.06">6%</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Product A - Lineitem Other Gross Profit</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Product A - LineitemA Revenue</TD></TR></TBODY></TABLE>

Trying to get it to return Which is the highest Margin and revenue Products.

Hope this helps adn thanx again for all the help.

 

[sanrv1f]

try this

Excel Workbook
	A	B	C	D	E	F
1	Product A - LineitemA Gross Profit	Product A - LineitemA Revenue	Product A - LineitemB Gross Profit	Product A - LineitemB Revenue	Product A - Lineitem Other Gross Profit	Product A - Lineitem Other Revenue
2	5%	8%	6%	4%	7%	5%
3	4%	4%	5%	5%	4%	5%
4	2%	6%	3%	1%	4%	6%
Sheet1

Excel 2003

Excel Workbook
	A	B	C	D
9	Max		Maximum Act
10	Gross Profit	Revenue	Gross Profit	Revenue
11	7%	8%	Product A - Lineitem Other Gross Profit	Product A - LineitemA Revenue
12	5%	5%	Product A - LineitemB Gross Profit	Product A - LineitemB Revenue
13	4%	6%	Product A - Lineitem Other Gross Profit	Product A - LineitemA Revenue
Sheet1

Excel 2003

Cell Formulas
Range	Formula
C11	=INDEX($A$1:$F$1,MATCH(1,INDEX(ISNUMBER(SEARCH(C$10,$A$1:$F$1))*($A2:$F2=A11),0),0))
D11	=INDEX($A$1:$F$1,MATCH(1,INDEX(ISNUMBER(SEARCH(D$10,$A$1:$F$1))*($A2:$F2=B11),0),0))

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

 

[Bogle]

Thanks Sankar!!!

That seems to be working!

Champion