Vlookup with function in table_array

Hi Guys,

I've been working on this off-and-on for the last couple of days with no
success. What I have is a worksheet where users enter information that is
then referenced against a norm table. What I'd like to do is allow them to
choose which norm table is used and have the information for that norm table
displayed. The tables are entered in separate worksheets following the first
worksheet.

I have been using a Vlookup function like this:
=VLOOKUP(N16,--------,14,TRUE), where N16 is the value that is looked up
initially, the blank is the Table_Array section, 14 is the column that the
needed value is found, and TRUE will choose the nearest the initial value.
The problem I'm having is that I'd like to insert a function in the array
blank (something I've been told recently is "impossible"...) or have the
array blank use the value of another cell with a generated value that
contains the correct worksheet (norm table) reference. I have tried to use
several different methods, but nothing is working.

=VLOOKUP(N16,T(N43),14,TRUE)
=VLOOKUP(N15,INDIRECT(N43),11,TRUE)

I'm really just an amateur, so I don't have a clue what to do. =) But, I
know you guys will find a solution. Thanks tons!

Josh =)

One way:

Name your tables (say, "table1", "table2") by selecting the table and
entering the name in the Name box (at the left of the formula bar).

Enter the table name in N43 (you could use a Validation dropdown list)

Then use

=VLOOKUP(N15,INDIRECT(N43),14, TRUE)

In article <[email protected]>,
"JoshuaEyer" <[email protected]> wrote:

> Hi Guys,
>
> I've been working on this off-and-on for the last couple of days with no
> success. What I have is a worksheet where users enter information that is
> then referenced against a norm table. What I'd like to do is allow them to
> choose which norm table is used and have the information for that norm table
> displayed. The tables are entered in separate worksheets following the first
> worksheet.
>
> I have been using a Vlookup function like this:
> =VLOOKUP(N16,--------,14,TRUE), where N16 is the value that is looked up
> initially, the blank is the Table_Array section, 14 is the column that the
> needed value is found, and TRUE will choose the nearest the initial value.
> The problem I'm having is that I'd like to insert a function in the array
> blank (something I've been told recently is "impossible"...) or have the
> array blank use the value of another cell with a generated value that
> contains the correct worksheet (norm table) reference. I have tried to use
> several different methods, but nothing is working.
>
> =VLOOKUP(N16,T(N43),14,TRUE)
> =VLOOKUP(N15,INDIRECT(N43),11,TRUE)
>
> I'm really just an amateur, so I don't have a clue what to do. =) But, I
> know you guys will find a solution. Thanks tons!
>
> Josh =)

Awesome! That worked perfectly! Thanks SO much. =)

Josh

"JE McGimpsey" wrote:

> One way:
>
> Name your tables (say, "table1", "table2") by selecting the table and
> entering the name in the Name box (at the left of the formula bar).
>
> Enter the table name in N43 (you could use a Validation dropdown list)
>
> Then use
>
> =VLOOKUP(N15,INDIRECT(N43),14, TRUE)
>
> In article <[email protected]>,
> "JoshuaEyer" <[email protected]> wrote:
>
> > Hi Guys,
> >
> > I've been working on this off-and-on for the last couple of days with no
> > success. What I have is a worksheet where users enter information that is
> > then referenced against a norm table. What I'd like to do is allow them to
> > choose which norm table is used and have the information for that norm table
> > displayed. The tables are entered in separate worksheets following the first
> > worksheet.
> >
> > I have been using a Vlookup function like this:
> > =VLOOKUP(N16,--------,14,TRUE), where N16 is the value that is looked up
> > initially, the blank is the Table_Array section, 14 is the column that the
> > needed value is found, and TRUE will choose the nearest the initial value.
> > The problem I'm having is that I'd like to insert a function in the array
> > blank (something I've been told recently is "impossible"...) or have the
> > array blank use the value of another cell with a generated value that
> > contains the correct worksheet (norm table) reference. I have tried to use
> > several different methods, but nothing is working.
> >
> > =VLOOKUP(N16,T(N43),14,TRUE)
> > =VLOOKUP(N15,INDIRECT(N43),11,TRUE)
> >
> > I'm really just an amateur, so I don't have a clue what to do. =) But, I
> > know you guys will find a solution. Thanks tons!
> >
> > Josh =)
>