What funtion to use?

  • Thread starter Thread starter fetzer
  • Start date Start date
F

fetzer

Not sure what function to use; vlookup, ISNA, MAtch, etc.. One of m
spreadsheets (sheet 2) has 36 different tables (not a pivot table) of
columns each where data is entered. The main spreadsheet (sheet 1) ha
a cell A:1, where I would like to enter the number 1 through 36. I
other words, if I wish to extract the data from table 5, I enter 5 i
cell A:1 which then looks for that particular table and extracts al
the data and enters it into the main spreadsheet. Any help would b
appreciated
 
Unclear how many rows each table has.
Suppose a table is just one row.
So here are the first three tables
1 apple 3 alpha
4 pear 6 beta
8 orange 11 gamma
On Sheet1 in A1 I enter a number, say 2
In Sheet1 in A2 I have formula =INDEX(Sheet2!A1:A36,Sheet1!A1)
This is copied across to D2
So Sheet1 displays
2
4 pear 6 beta


If you tables are more than one row we can make adjusts. Please tell us what
you need.
best wishes
 
A relatively easy way to do this would be to give each of your 36 tables a
name. The first part of all table names would be "common", such as MyTable
with a number from 1 to 36 to indicate a specific table. So you would have
MyTable1, MyTable2, MyTable3 ... MyTable34, MyTable35, MyTable36

Then in your VLookup formulas that need to reference a table based on the
entry in A1, your formula would look something like:
=VLOOKUP(A3,INDIRECT("MyTable" & A1),2,FALSE)
The important part here is the INDIRECT() portion, which will take the
contents of A1 and concatenate it to the 'common' table name "MyTable" to
pick up the correct table.

To give a table a name, select all of the cells in the table, then type its
name into the 'Name Box' and press the [Enter] key. The Name Box is the box
in the formula bar that usually shows you the address of the cell currently
selected/active. Look up Excel's Help for 'Define named cell references or
ranges' for more help on this subject.
 
Back
Top