cell reference from hlookup

[warrenb]

I am using the HLOOKUP function to find a value in a table.
I also need to confirm the cell reference. I have tried nesting my HLOOKUP
within the OFFSET function, but HLOOKUPs return variables rather than
references.

Here's my (unsuccessful) formula:

=OFFSET(HLOOKUP($A$1,$A$4:$H$13,4,0),0,1)

How can I make a 'lookup' return a cell reference?

Thanks!
Warren

[Arvi Laanemets]

Hi

p.e.
=OFFSET($A$4,1,MATCH($A$1,$A$4:$H$4,0)-1)
reads column heading value from cell A1, looks for this header in row 4, and
for it's position, and returns according value from row 5.


--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )


"warrenb" <[email protected]> wrote in message
news:[email protected]...
>I am using the HLOOKUP function to find a value in a table.
> I also need to confirm the cell reference. I have tried nesting my
> HLOOKUP
> within the OFFSET function, but HLOOKUPs return variables rather than
> references.
>
> Here's my (unsuccessful) formula:
>
> =OFFSET(HLOOKUP($A$1,$A$4:$H$13,4,0),0,1)
>
> How can I make a 'lookup' return a cell reference?
>
> Thanks!
> Warren
>

[Aladin Akyurek]

=INDEX($A$7:$H$7,MATCH(A1,$A$4:$H$4,0))

=CELL("Address",INDEX($A$7:$H$7,MATCH(A1,$A$4:$H$4,0)))

The first formula is equivalent to the HLOOKUP formula that you have.

warrenb wrote:
> I am using the HLOOKUP function to find a value in a table.
> I also need to confirm the cell reference. I have tried nesting my HLOOKUP
> within the OFFSET function, but HLOOKUPs return variables rather than
> references.
>
> Here's my (unsuccessful) formula:
>
> =OFFSET(HLOOKUP($A$1,$A$4:$H$13,4,0),0,1)
>
> How can I make a 'lookup' return a cell reference?
>
> Thanks!
> Warren
>

[warrenb]

Thanks Avri; just what I was looking for!

"Arvi Laanemets" wrote:

> Hi
>
> p.e.
> =OFFSET($A$4,1,MATCH($A$1,$A$4:$H$4,0)-1)
> reads column heading value from cell A1, looks for this header in row 4, and
> for it's position, and returns according value from row 5.
>
>
> --
> Arvi Laanemets
> ( My real mail address: arvi.laanemets<at>tarkon.ee )
>
>
> "warrenb" <[email protected]> wrote in message
> news:[email protected]...
> >I am using the HLOOKUP function to find a value in a table.
> > I also need to confirm the cell reference. I have tried nesting my
> > HLOOKUP
> > within the OFFSET function, but HLOOKUPs return variables rather than
> > references.
> >
> > Here's my (unsuccessful) formula:
> >
> > =OFFSET(HLOOKUP($A$1,$A$4:$H$13,4,0),0,1)
> >
> > How can I make a 'lookup' return a cell reference?
> >
> > Thanks!
> > Warren
> >
>
>
>

[warrenb]

Thanks Aladin - This is excellent stuff which I will use lots and lots and
lots...! :o)

"Aladin Akyurek" wrote:

>
> =INDEX($A$7:$H$7,MATCH(A1,$A$4:$H$4,0))
>
> =CELL("Address",INDEX($A$7:$H$7,MATCH(A1,$A$4:$H$4,0)))
>
> The first formula is equivalent to the HLOOKUP formula that you have.
>
> warrenb wrote:
> > I am using the HLOOKUP function to find a value in a table.
> > I also need to confirm the cell reference. I have tried nesting my HLOOKUP
> > within the OFFSET function, but HLOOKUPs return variables rather than
> > references.
> >
> > Here's my (unsuccessful) formula:
> >
> > =OFFSET(HLOOKUP($A$1,$A$4:$H$13,4,0),0,1)
> >
> > How can I make a 'lookup' return a cell reference?
> >
> > Thanks!
> > Warren
> >
>