Vlookup - problem

[Guest]

This is what I am trying to do:
There is data on one worksheet that I want to show on another worksheet.
Here's the problem: The data that I need to find will consistently change so
what I have is a drop down menu (limitations) that lists all of the possible
values that could be looked up.

I would like to select a value from the drop down menu, and then using
VLOOKUP or some other function, show all of the data and the adjacent cells
that match the item selected in VLOOKUP.

For example:
Drop down menu (worksheet 1, cell A1) lists: G. Bush, J. Kerry, R. Nader
User selects one of the choices, i.e. G. Bush
Then excel searches worsheet 2, column A for all cells containing "G. Bush"
Then excel lists on worsheet 1, cell A2 through A100 all references, and in
columns B through AA all of the information that is listed in columns B
through AA of workseet 2.

Is this possible? If so, how is it done. Lastly, is it possible to take it
even further and say that not only must column A match what is in the drop
down menu, but column B must match something from another drop down, and C
from another menu, and so on...

Thanks, this has really been posing me problems...

Best,

 

[Max]

Assume you have

In Sheet2
-------------
The table below, in cols A to D
headers in row1, data from row2 down

Name Field1 Field2 Field3
Name1 Data1 Data11 Data111
Name2 Data2 Data12 Data112
Name3 Data3 Data13 Data113
Name1 Data4 Data14 Data114
Name2 Data5 Data15 Data115
Name3 Data6 Data16 Data116
Name1 Data7 Data17 Data117
Name2 Data8 Data18 Data118
Name3 Data9 Data19 Data119
etc

Use an empty col to the right of all data, say col AC

Put in AC1: =Sheet1!A1

Put in AC2: =IF($A2="","",IF($A2=AC$1,ROW(),""))
Copy AC2 down by as many rows as data is expected
in the table, say down to AC1000?

In Sheet1
------------
Assume
you have a DV in A1 to select: Name1, Name2, Name3
and in B1, C1, D1 are DVs to select: Field1, Field2, Field3

Put in A2:

=IF(ISERROR(MATCH(SMALL(Sheet2!$AC:$AC,ROW(A1)),Sheet2!$AC:$AC,0)),"",OFFSET
(Sheet2!$A$1,MATCH(SMALL(Sheet2!$AC:$AC,ROW(A1)),Sheet2!$AC:$AC,0)-1,))

Put in B2:

=IF(OR(ISERROR(MATCH(SMALL(Sheet2!$AC:$AC,ROW(B1)),Sheet2!$AC:$AC,0)),ISERRO
R(MATCH(B$1,Sheet2!$1:$1,0))),"",OFFSET(Sheet2!$A$1,MATCH(SMALL(Sheet2!$AC:$
AC,ROW(B1)),Sheet2!$AC:$AC,0)-1,MATCH(B$1,Sheet2!$1:$1,0)-1))

Copy B2 across to D2

Select A22, fill down by as many rows as
was done in Sheet2's col AC, i.e. down to D1000

The above set-up will extract the relevant data over
from Sheet2 according to what's specified
in the DVs in A1, and in B11

Adapt / extend to suit
---

Note:

In Sheet1
-------------
If you just want to extract all relevant rows from Sheet2
by the name selected in the DV in A1 (in Sheet1)
(that's actually the 1st part of your Q <g>)

Paste the headers from Sheet2's B11
into Sheet1's B1;D1

Put in A2 (in Sheet1):

=IF(ISERROR(MATCH(SMALL(Sheet2!$AC:$AC,ROW(A1)),Sheet2!$AC:$AC,0)),"",OFFSET
(Sheet2!$A$1,MATCH(SMALL(Sheet2!$AC:$AC,ROW(A1)),Sheet2!$AC:$AC,0)-1,COLUMN(
A1)-1))

[The formula in A2 is identical to that given earlier for A2,
except for the last part: ... COLUMN(A1)-1
to define the cols param for the OFFSET ]

Copy A2 across to D2, fill down by as many rows as
was done in Sheet2's col AC, i.e. down to D1000