Data validation list & vlookup

[Thomp]

Hi, I think what I am trying to do is have the data valadation lists
be used to pull data from one spreadsheet over from another
spreadsheets table. Easy enough to use a vlookup for one criteria but
it gets tricky when I try to get data based on two criterias.

Example would be first worksheet has pull down list with cars in cell
A1, Ford,Chevy,Dodge,etc ..the second pull down list has the colors in
cell C1: red,blue,white,etc,

I want to pull data from a second spreadsheet that has the table with
the data table

Color Ford Chevy Dodge
White 1 2 1
Red 1 4 2
Blue 3 0 1

The problem is I need two criteria. I need the data based on the
criteria selected by the two drop down list..Example If I need to know
who many blue Fords I have then I select the pull down for Ford and
Blue and my answer is 3.


Please don't suggest a filter as I am creating a form and don't want
to filter info but rather do some kind of vlookup or something based
on these two criteria

thank,
Bill

 

[T. Valko]

Try this:

=VLOOKUP(C1,Sheet2!A14,MATCH(A1,Sheet2!A11,0),0)

 

[Gord Dibben]

I would suggest using dependent DV lists rather than two distinct DV
dropdowns.

See Debra Dalgleish's site for instructions.

http://www.contextures.on.ca/xlDataVal02.html

Pick Ford from the list and you are presented with a dropdown of colors
available from Ford.

Select Blue and have a VLOOKUP formula pull 3 from Blue Fords


Gord Dibben MS Excel MVP

 

[Thomp]

Try this:

=VLOOKUP(C1,Sheet2!A14,MATCH(A1,Sheet2!A11,0),0)

--
Biff
Microsoft Excel MVP












- Show quoted text -

For some reason it doesn't seem to work. Here is the actual formula I
am using and the spreadsheet data



=VLOOKUP(A3,Sheet1!A1:J9,MATCH(B3,Sheet1!A1:J9,0),0)

KPI Southwest Southeast Northeast Northwest Central San Francisco New
England Northern Border Southern Border
KPI_1a 11 28 18 49 21 3 22 4 2
KPI_1b 12 20 30 25 54 32 22 1 5
KPI_1c 10 5 5 5 5 5 12 11 10
KPI_1d 34 22 55 55 56 42 41 11 12
KPI_2a 12 29 30 51 22 4 23 5 4
KPI_2b 2 15 24 21 54 61 20 2 4
KPI_2c 7 4 3 2 7 5 4 10 7
KPI_2d 30 20 50 45 42 41 40 10 10


Sorry, Paste doesn't work so well on this site but I hope you get the
picture.

The two data valadation list are in cells A3 and B1 on Sheet 2 (3)

thanks,
Bill

 

[T. Valko]

=VLOOKUP(A3,Sheet1!A1:J9,MATCH(B3,Sheet1!A1:J9,0),0)

The two data valadation list are in cells A3 and B1

VLOOKUP(A3... is the vertical lookup
MATCH(B3... is the horizontal lookup

The horizontal lookup must be to a single row.

Try it like this:

=VLOOKUP(A3,Sheet1!A1:J9,MATCH(B1,Sheet1!A1:J1,0),0)

Note how I changed the horizontal lookup range to a single row.

--
Biff
Microsoft Excel MVP

Try this:

=VLOOKUP(C1,Sheet2!A14,MATCH(A1,Sheet2!A11,0),0)

--
Biff
Microsoft Excel MVP












- Show quoted text -

For some reason it doesn't seem to work. Here is the actual formula I
am using and the spreadsheet data



=VLOOKUP(A3,Sheet1!A1:J9,MATCH(B3,Sheet1!A1:J9,0),0)

KPI Southwest Southeast Northeast Northwest Central San Francisco New
England Northern Border Southern Border
KPI_1a 11 28 18 49 21 3 22 4 2
KPI_1b 12 20 30 25 54 32 22 1 5
KPI_1c 10 5 5 5 5 5 12 11 10
KPI_1d 34 22 55 55 56 42 41 11 12
KPI_2a 12 29 30 51 22 4 23 5 4
KPI_2b 2 15 24 21 54 61 20 2 4
KPI_2c 7 4 3 2 7 5 4 10 7
KPI_2d 30 20 50 45 42 41 40 10 10


Sorry, Paste doesn't work so well on this site but I hope you get the
picture.

The two data valadation list are in cells A3 and B1 on Sheet 2 (3)

thanks,
Bill