Show Specific Cells if multiple dropdowns equal

Question

is it possible to have Excel show all data from specific columns and rows by way of a Vlookup up or index match using multiple dropdowns to give the required search variables.

E.g.:

Drop down 1 has London selected,
Dropdown 2 has telephone selected,
Dropdown 3 has internal selected and
Dropdown 4 has PM selected.

The formula will then search worksheet 2 that has the data set in columns and return the result of the entire matching column into a cell on worksheet 1.

Hope that makes sense but if you need any more details please ask. Thanks

micstr micstr · Accepted Answer · 2016-02-12T09:27:46

I have done the following in Excel (not using VBA) for when there is a unique match. You create a key that joins all the ID fields at the start of the table. I used a spacer "-" to make it easy to read the parts.

So if your Data Table looks like this:

Key / City / Contact / Internal / AM or PM / Other data fields

So in Column A of your data block you have a constructed key:

= City & "-" & Contact & "-" & Internal & "-" & AM

i.e. so in A2 its:

= B2 & "-" & C2 &  "-" & D2 & "-" & E2

I then create this key from the dropdowns and look this up.

So your dropdowns are looking up key string

"London-Telephone-Internal-PM"

As your dropdowns change your key changes and then you are using this 4 in 1 key to find your data in your VLOOKUP.

Cheesy but easy. Others may have smarter methods.

EDIT MINI EXAMPLE USING TWO IDs

My Data Table

Col A / B / C / D
ID / City / Telephone / Other

"London-Tele" / London / Tele / Likes fish and chips
"London-Mobile" / London / Mobile / Dont call before 9am
"Tokyo-Mobile" / Tokyo / Mobile / Likes sushi
...

Note first column ID is created as a formula here in A2 you have B2&"-"&C2

my lookup

I get user selection from my drop down boxes. Dropdowns feed me "London-Tele" into cell call it rngSelection. This cell is getting the answer from Dropdown 1 and then Answer Dropdown 2 and then ans1&"-"&ans2 to make the key.

In my other sheet I have to get the Other data in column D

= VLOOKUP(rngSelection, rngDataTable, 4, FALSE)

which will return in this cell the answer

Likes fish and chips

P.S. I like to do this to error trap

= IF( ISERROR(VLOOKUP(rngSelection, rngDataTable, 4, FALSE)),"Not found",
 VLOOKUP(rngSelection, rngDataTable, 4, FALSE)

Show Specific Cells if multiple dropdowns equal

1 Answers