Name Manager For A Lookup

#1 (**permalink**) 30th June 2009, 04:45 AM

------ Register to get rid of these "In Post" ads! ------

What sort of OFFSET+LOOKUP combination formula should be named so that when
simply used in B1 as:

=NAME

would work as looking into a named range and returning the corresponding
entry from it's third column, the cell on the left, i.e. A1?

Thanx in advance.

--
Best Regards,

Faraz

#2 (**permalink**) 30th June 2009, 05:00 AM

------ Register to get rid of these "In Post" ads! ------

Hi,

If the item in A1 is to be found in the first column of the named range then

=VLOOKUP(A1,myRange,3,TRUE)

should do the trick, or the slightly shorter version

=VLOOKUP(A1,myRange,3,)

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

"Faraz A. Qureshi" wrote:

> What sort of OFFSET+LOOKUP combination formula should be named so that when
> simply used in B1 as:
>
> =NAME
>
> would work as looking into a named range and returning the corresponding
> entry from it's third column, the cell on the left, i.e. A1?
>
> Thanx in advance.
>
> --
> Best Regards,
>
> Faraz

#3 (**permalink**) 30th June 2009, 05:46 AM

------ Register to get rid of these "In Post" ads! ------

Sorry Shane,

But it seems like I was unable to clarify the example.

The cell could be any, not only in Column A. Fopr example if you insert the
formula in Z100, the Y100 is sought to be considered.
--
Best Regards,

Faraz

"Shane Devenshire" wrote:

> Hi,
>
> If the item in A1 is to be found in the first column of the named range then
>
> =VLOOKUP(A1,myRange,3,TRUE)
>
> should do the trick, or the slightly shorter version
>
> =VLOOKUP(A1,myRange,3,)
>
> --
> If this helps, please click the Yes button.
>
> Cheers,
> Shane Devenshire
>
>
> "Faraz A. Qureshi" wrote:
>
> > What sort of OFFSET+LOOKUP combination formula should be named so that when
> > simply used in B1 as:
> >
> > =NAME
> >
> > would work as looking into a named range and returning the corresponding
> > entry from it's third column, the cell on the left, i.e. A1?
> >
> > Thanx in advance.
> >
> > --
> > Best Regards,
> >
> > Faraz

#4 (**permalink**) 30th June 2009, 05:59 AM

------ Register to get rid of these "In Post" ads! ------

Faraz

Do you mean..
=INDIRECT(ADDRESS(ROW(),COLUMN()-1))

If this post helps click Yes
---------------
Jacob Skaria

"Faraz A. Qureshi" wrote:

> What sort of OFFSET+LOOKUP combination formula should be named so that when
> simply used in B1 as:
>
> =NAME
>
> would work as looking into a named range and returning the corresponding
> entry from it's third column, the cell on the left, i.e. A1?
>
> Thanx in advance.
>
> --
> Best Regards,
>
> Faraz

#5 (**permalink**) 30th June 2009, 06:24 AM

------ Register to get rid of these "In Post" ads! ------

Thanx Jacob!!!

Never had an idea of using ADRESS()!!!

XClent!
--

Best Regards,

Faraz

"Jacob Skaria" wrote:

> Faraz
>
> Do you mean..
> =INDIRECT(ADDRESS(ROW(),COLUMN()-1))
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Faraz A. Qureshi" wrote:
>
> > What sort of OFFSET+LOOKUP combination formula should be named so that when
> > simply used in B1 as:
> >
> > =NAME
> >
> > would work as looking into a named range and returning the corresponding
> > entry from it's third column, the cell on the left, i.e. A1?
> >
> > Thanx in advance.
> >
> > --
> > Best Regards,
> >
> > Faraz

Thread Tools
Show Printable Version Email this Page Mark this thread as solved
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)