Lookup formula with offset needed

[sacman]

I am in need of a formula that will return a cell's text offset from a lookup table. For example, I am using an HLookup formula to look for a product family and then return the part number. The problem is the part number is located in a cell directly below and one cell to the right. I don't want to move the product family cells as they are used as headings and would appear off-centered. The cell directly below the product family is a value.



<TABLE style="WIDTH: 192pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=256><COLGROUP><COL style="WIDTH: 48pt" span=4 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 96pt; HEIGHT: 12.75pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 height=17 width=128 colSpan=2>FAMILY A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: black 1pt solid" class=xl65 width=128 colSpan=2>FAMILY B</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68 height=18>155</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl67>PART X</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl68>98</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: windowtext 1pt solid" class=xl67>PART Y</TD></TR></TBODY></TABLE>


I have other applications for an offset like this as well.

Thanks for any help.

Allen

 

[Gerald Higgins]

Let's say Family A is in cell A1
Family B is in cell C1
155 is in cell A2
Part X is in cell B2
98 is in cell C2
Part Y (just to make it different from Part X) is in cell D2.

Let's say your input, of "Family B" is in cell F1.

=OFFSET(A1,1,MATCH(F1,A1:D1,0),1,1)

will return "Part Y".

 

[sacman]

Let's say Family A is in cell A1
Family B is in cell C1
155 is in cell A2
Part X is in cell B2
98 is in cell C2
Part Y (just to make it different from Part X) is in cell D2.

Let's say your input, of "Family B" is in cell F1.

=OFFSET(A1,1,MATCH(F1,A1:D1,0),1,1)

will return "Part Y".

Perfect! Thank you very much.

Allen