Excel 2007 : Lookup Data Using Drop Down Menus

**The_Snook** · 06-29-2010, 09:19 AM

Afternoon all,

Is it possible to look up data using drop down menus? I've attached an example of what i'm trying to achieve. I'd like to be able to change the drop downs in cells A1 and B1 and pull through the data from the relevant tab into cells B2 to B4.

Thanks in advance,

Snook

**romperstomper** · 06-29-2010, 09:34 AM

Yes - using INDIRECT, and INDEX and MATCH. see attached.
Note: I had to alter your date dropdown to use actual dates rather than just month names.

**sailepaty** · 06-29-2010, 11:47 AM

One more option,

Menu->Formulas->Define Name

Name:SOURCE
Refers to:=CHOOSE(MATCH(Report!$B$1,{"Budget","Forecast","Rolling"},0),Budget!$A$1:$M$4,Forecast!$A$1:$M$4,Rolling!$A$1:$M$4)

Now formula in B2 of Report sheet and copy down.

=INDEX(SOURCE,MATCH($A2,INDEX(SOURCE,0,1),0),MATCH($A$1,INDEX(SOURCE,1,0),0))

Regards