Excel Lookup function

[Howard]

I need to put more than one lookup table on a sheet, and define which table
to use when performing a lookup function. Is this possible, and if so, how?
Thanks

 

[Gary''s Student]

=IF(A1=1,VLOOKUP(),VLOOKUP())

 

[Howard]

Thanks for the reply so quickly, but I probable didn't make myself clear. I
want to put more than one lookup table on a worksheet, and then different
cells would use different tables to determine a result. It seems that I
would have to identify each table, and then the =IF function would have to
know which table to use.

 

[Shane Devenshire]

Hi,

That is what was suggested in effect:

=IF(A1="Accounting",VLOOKUP(C1,Table1,2,FALSE),VLOOKUP(C1,Table2,2,FALSE)

or if there are a lot of possible tables (departments) why not something
like this:

=VLOOKUP(C1,INDIRECT(A1),2,FALSE)

In this case A1 would contain the department name and each table would be
named with one of the department names. So if you entered Accounting in A1
the vlookup would be doing

=VLOOKUP(C1,Accounting,2,FALSE)

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Howard]

Thanks Shane, and you too Gary. I'll give this a try.

 

[Howard]

Hate to keep being a pest, but I'm having trouble figuring out how to design
the table in Excel 2007 so that I name the table "Accounting", for example.
I've tried looking at Excel Help, but no real luck.

 

[Shane Devenshire]

Hi,

Just highlight a range of one of the tables and select the Name Box (on the
left of the Formula Bar where you see the cell address) and type in a name
like Accounting (no spaces) and press Enter. (Pressing Enter is necessary).

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[Howard]

Thanks again. I've got plenty to work on now.

 

[Gail]

I am trying to autofill data from one spreadsheet to another, can you assist
me?

 

[Fred Smith]

Gail,

Don't hijack threads. For a new topic, start a new thread. And when you do,
make sure you include enough information so that a solution can be
proffered.

Regards,
Fred.

 

[MyVeryOwnSelf]

I need to put more than one lookup table on a sheet, and define which

table to use when performing a lookup function. Is this possible, and
if so, how? Thanks

To contrive an example, suppose

* there are four lookup tables: B1:C100, D1:E100, F1:G100, H1:I100.

* The value to be looked up is in A2

* A3 contains a number from 1 to 4 indicating which table to use.

Then in A1 put this function to do the lookup:
=VLOOKUP(A2,OFFSET($B$1,0,CHOOSE(A3,0,2,4,6,8),100,2),2,FALSE)