INDEX Function... Can you index with two variables?

Mr. Walnuts

Board Regular
Joined
Aug 8, 2005
Messages
176
I am using the INDEX function currently on a roster I have set up. The way it is set up is as follows:

Sheet 3 = Main database with all information stored. 20 columns, each column's range is named to make indexing easier. Column A contains personnel names, and subsequent columns contain information on the person named in column A.

Sheet 1 contains a header row consisting of 13 of the 20 column titles from the database sheet. Column A on sheet 1 contains list boxes in each cell from the "Names" column in the database sheet. upon choosing a name from the drop box, the index function fills in the cells in the 12 cells adjacent to the name box with the information it indexed on the database.

Here is my question.
If I wanted to use this same methodology for indexing using not only the names column... but I woud like the header row cells to be drop down boxes as well. this way I could not only choose the personnel whose information to index... but also the information. is this posible?
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
If I understand what you're trying to do correctly, i think Vlookup with Match should work.

In cell B2 on sheet 1, assuming column A has the names, and row 1 the various information fields.

=vlookup($a2,sheet3!$A$1:EndofRange,match(b$1,sheet3!$B$1:$X$1,0),False)

You will need to adjust the cell references to suit. you can then drag the formula across and down
 

Mr. Walnuts

Board Regular
Joined
Aug 8, 2005
Messages
176
A simplified version will probably suffice for the time being.

the database is on top... the table below... the top row of the table is all dropboxes... the left-most column is all drop boxes with names. I need to be able to select the column head... and select the name.. and have the correct information in the corresponding cells. unfortunately in the HTML sheet below, empty cells were left unformatted. Imagine that the table on bottom has cells in it. haha.
Book1
ABCDEFG
1Database
2NameRankSSNShoeBloodGenderUnit
3JoePVT1118A+M4-2
4BobPFC2229O-M4-2
5HarryLT3338AB-M4-2
6ChuckCPT4449B-M4-2
7BillSSG55510A-M4-2
8NoahSPC66611A+M4-2
9MethuselaSGM7779O-UNK4-2
10
11
12
13
14
15
16
17Table
18NameRankGenderShoe
19Joe1
20
21Bill
22
23
24
25
26
27
28
Sheet1
 

Mr. Walnuts

Board Regular
Joined
Aug 8, 2005
Messages
176
oh yea... and ignore that miserable attempt at a formula in cell B19

haha... I forgot to erase it before posting.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

=INDEX($B$3:$G$9,MATCH($A19,$A$3:$A$9,0),MATCH(B$18,$B$2:$G$2,0))
 

Mr. Walnuts

Board Regular
Joined
Aug 8, 2005
Messages
176
Outstanding.
Just out of curiosity...
Is there somewhere I can get a list of function syntaxes?..
I mean something better than what excel gives you on mouseover or when writing the formula... that little thing that pops up tells me very little.
I do this alot but I often don't know exactly what the arguement is supposed to be for the function. the abbreviated syntax doesn't do it for me.

Thanks by the way.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201

ADVERTISEMENT

Outstanding.
Just out of curiosity...
Is there somewhere I can get a list of function syntaxes?..
I mean something better than what excel gives you on mouseover or when writing the formula... that little thing that pops up tells me very little.
I do this alot but I often don't know exactly what the arguement is supposed to be for the function. the abbreviated syntax doesn't do it for me.

Thanks by the way.

Excel's Help describes the syntax of each function.
 

Watch MrExcel Video

Forum statistics

Threads
1,112,796
Messages
5,542,549
Members
410,560
Latest member
1ndependent
Top