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?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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
 
Upvote 0
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
 
Upvote 0
oh yea... and ignore that miserable attempt at a formula in cell B19

haha... I forgot to erase it before posting.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top