Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 68
Default Excel Lookup function

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
--
Howard
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Excel Lookup function

=IF(A1=1,VLOOKUP(),VLOOKUP())
--
Gary''s Student - gsnu200816
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 68
Default Excel Lookup function

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.
--
Howard


"Gary''s Student" wrote:

=IF(A1=1,VLOOKUP(),VLOOKUP())
--
Gary''s Student - gsnu200816

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Excel Lookup function

Hi,

That is what was suggested in effect:

=IF(A1="Accounting",VLOOKUP(C1,Table1,2,FALSE),VLO OKUP(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" wrote:

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.
--
Howard


"Gary''s Student" wrote:

=IF(A1=1,VLOOKUP(),VLOOKUP())
--
Gary''s Student - gsnu200816

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 68
Default Excel Lookup function

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


"Shane Devenshire" wrote:

Hi,

That is what was suggested in effect:

=IF(A1="Accounting",VLOOKUP(C1,Table1,2,FALSE),VLO OKUP(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" wrote:

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.
--
Howard


"Gary''s Student" wrote:

=IF(A1=1,VLOOKUP(),VLOOKUP())
--
Gary''s Student - gsnu200816



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 68
Default Excel Lookup function

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.
--
Howard


"Shane Devenshire" wrote:

Hi,

That is what was suggested in effect:

=IF(A1="Accounting",VLOOKUP(C1,Table1,2,FALSE),VLO OKUP(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" wrote:

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.
--
Howard


"Gary''s Student" wrote:

=IF(A1=1,VLOOKUP(),VLOOKUP())
--
Gary''s Student - gsnu200816

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,346
Default Excel Lookup function

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" wrote:

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.
--
Howard


"Shane Devenshire" wrote:

Hi,

That is what was suggested in effect:

=IF(A1="Accounting",VLOOKUP(C1,Table1,2,FALSE),VLO OKUP(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" wrote:

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.
--
Howard


"Gary''s Student" wrote:

=IF(A1=1,VLOOKUP(),VLOOKUP())
--
Gary''s Student - gsnu200816

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 68
Default Excel Lookup function

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


"Shane Devenshire" wrote:

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" wrote:

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.
--
Howard


"Shane Devenshire" wrote:

Hi,

That is what was suggested in effect:

=IF(A1="Accounting",VLOOKUP(C1,Table1,2,FALSE),VLO OKUP(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" wrote:

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.
--
Howard


"Gary''s Student" wrote:

=IF(A1=1,VLOOKUP(),VLOOKUP())
--
Gary''s Student - gsnu200816

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 47
Default Excel Lookup function

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

"Howard" wrote:

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


"Shane Devenshire" wrote:

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" wrote:

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.
--
Howard


"Shane Devenshire" wrote:

Hi,

That is what was suggested in effect:

=IF(A1="Accounting",VLOOKUP(C1,Table1,2,FALSE),VLO OKUP(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" wrote:

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.
--
Howard


"Gary''s Student" wrote:

=IF(A1=1,VLOOKUP(),VLOOKUP())
--
Gary''s Student - gsnu200816

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,389
Default Excel Lookup function

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.

"Gail" wrote in message
...
I am trying to autofill data from one spreadsheet to another, can you
assist
me?

"Howard" wrote:

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


"Shane Devenshire" wrote:

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" wrote:

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.
--
Howard


"Shane Devenshire" wrote:

Hi,

That is what was suggested in effect:

=IF(A1="Accounting",VLOOKUP(C1,Table1,2,FALSE),VLO OKUP(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" wrote:

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.
--
Howard


"Gary''s Student" wrote:

=IF(A1=1,VLOOKUP(),VLOOKUP())
--
Gary''s Student - gsnu200816




  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 143
Default Excel Lookup function

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)
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Excel lookup function please Victor Delta[_2_] Excel Discussion (Misc queries) 12 September 11th 08 10:53 PM
Excel 2003 Lookup function Newfie809 Excel Worksheet Functions 3 November 12th 07 07:06 PM
advanced use of the Excel lookup function Norm Excel Discussion (Misc queries) 2 October 12th 06 01:27 PM
Lookup function in Excel Feldy Excel Worksheet Functions 4 February 17th 05 05:25 PM
IF/ LOOKUP FUNCTION - Excel 2000 Chandrashekhar Excel Worksheet Functions 3 November 5th 04 12:02 PM


All times are GMT +1. The time now is 06:43 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"