Using two or more drop downs with VLOOKUP

[bp32]

Hi:

I have an analytics sheet where I have a client name chosen from a drop down via validation. The problem I am running into is that while I want information from a data table to populate a cell based on the selection from the validation, I also want the cell to populate based on a second drop down box (basically selecting a time frame). Take the example data table below:

Client Jan 2009 Jan 2009 Target etc etc
Client A 1111 1290
Client B 900 750

I would like the user to be able to select Client B and then, from a separate drop down, also choose, say, Jan 2009 Target and then another cell would populate with, in this case, 750.

How can I do this?

Many thanks,

bp32

 

[gardnertoo]

Since your data is stored in a matrix format, an INDEX MATCH combination will work well:

Book2
	A	B	C	D	E	F	G	H
1		client
2	data wanted		Jan 09 actual	Jan 09 target	Feb 09 actual	Feb 09 target	Mar 09 actual	Mar 09 target
3		Client A	1111	1290	598	628	879	661
4		Client B	900	750	853	613	761	840
5		Client C	711	446	587	418	472	554
6		Client D	804	453	727	876	898	637
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21	Choose client:	Client B
22	Choose data:	Jan 09 target
23	Result:	750
Sheet3

 

[bp32]

Fantastic, I will give it a shot.

Thanks!

 

[bp32]

OK, so here is an added dimension:

The data that I want to pull from is contained in separate sheets. So the drop downs are validated with information from the current sheet but what I want to INDEX MATCH is coming from other sheets. How would the formula change?

 

[gardnertoo]

Instead of:

=INDEX(C3:H6,MATCH(B21,B3:B6,FALSE),MATCH(B22,C2:H2,FALSE))

You would add the sheet reference. If the data you want to retrieve is on Sheet1 for example, the formula would look like this:

=INDEX(C3:H6,MATCH(B21,Sheet1!B3:B6,FALSE),MATCH(B22,Sheet1!C2:H2,FALSE))

 

[bp32]

Excellent, but what about the first statement in the formula (C3:H6) as that data would also be on another sheet, or will the formula simply look for that data on the other sheets because of the new notations?

 

[gardnertoo]

You must specify the other sheet for those references too, following the same pattern.

 

[bp32]

So here is the formula:

=INDEX('$'C3:BB150,MATCH(B33,'$'A2:A104,FALSE),MATCH(A34,'$C1:BB1,FALSE))

Where $ is the name of the other sheet. B33 and A34 are cells where the values to be indexed will be entered.

Excel is telling me there is an error with the formula. Am I not referencing the sheet properly? Is the problem that its name is a $ sign?

Thanks!

 

[gardnertoo]

Here's a suggested formula:

=INDEX('$'!C3:BB150,MATCH(B33,'$'!A3:A150,FALSE),MATCH(A34,'$'!C1:BB1,FALSE))

The problem with the formula you posted was the lack of exclamation marks between the sheetname and the cell addresses. I'm also wondering about some of your cell references, in red:

=INDEX('$'C3:BB150,MATCH(B33,'$'A2:A104,FALSE),MATCH(A34,'$C1:BB1,FALSE))

1) Why don't the rows you're INDEXing from line up with the rows you're MATCHing, both in quantity and starting point?
2) Is row 2 and column B being omitted from the INDEXing on purpose?

In my formula at the top of this post, I've assumed that the answers to those two questions are 1) "Oops, they should be the same" and 2) "Yes, row 2 and column B have additional header information, not data"

 

[bp32]

Bingo! Thanks again, worked perfectly!