Vlookup top to bottom & not left to right

Hi all, is it possible to look up a number on the top of the sheet,
let's say a1, b1, c1,d1,etc.. and get the value in the last used cell of
the column or must one copy and past them left to right ??

thanks in advance,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Did you try HLookup?

-Mike

"Les Stout" <[email protected]> wrote in message
news:[email protected]...
> Hi all, is it possible to look up a number on the top of the sheet,
> let's say a1, b1, c1,d1,etc.. and get the value in the last used cell of
> the column or must one copy and past them left to right ??
>
> thanks in advance,
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***

find the column

=Match("Price",A1:Z1,0)

once you have the column then

Use a number larger than any in the column (assume you are looking at
numbers)
match(999999999999,F1:F500,1)

so

Putting it all together:

=OFFSET($A$1,MATCH(999999,OFFSET($A$1,1,MATCH("Price",$B$1:$Z$1),500,1),1),M
ATCH("Price",$B$1:$Z$1))

--
Regards,
Tom Ogilvy


"Les Stout" <[email protected]> wrote in message
news:[email protected]...
> Hi all, is it possible to look up a number on the top of the sheet,
> let's say a1, b1, c1,d1,etc.. and get the value in the last used cell of
> the column or must one copy and past them left to right ??
>
> thanks in advance,
>
> Les Stout
>
> *** Sent via Developersdex http://www.developersdex.com ***

Thanks mike.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Tom, will try it in the morning.

best regards,

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

I guess you said a number in the first row rather than a text header, so
just replace the "Price" with a number

=OFFSET($A$1,MATCH(999999,OFFSET($A$1,1,MATCH(321,$B$1:$Z$1,0),500,1),1),M
ATCH(321,$B$1:$Z$1,0))

also in those two match formulas, I forgot to put the third argument in
which should be a zero (0). that would be true for text or number.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <[email protected]> wrote in message
news:Om5mGR%[email protected]...
> find the column
>
> =Match("Price",A1:Z1,0)
>
> once you have the column then
>
> Use a number larger than any in the column (assume you are looking at
> numbers)
> match(999999999999,F1:F500,1)
>
> so
>
> Putting it all together:
>
>
=OFFSET($A$1,MATCH(999999,OFFSET($A$1,1,MATCH("Price",$B$1:$Z$1),500,1),1),M
> ATCH("Price",$B$1:$Z$1))
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Les Stout" <[email protected]> wrote in message
> news:[email protected]...
> > Hi all, is it possible to look up a number on the top of the sheet,
> > let's say a1, b1, c1,d1,etc.. and get the value in the last used cell of
> > the column or must one copy and past them left to right ??
> >
> > thanks in advance,
> >
> > Les Stout
> >
> > *** Sent via Developersdex http://www.developersdex.com ***
>
>