Dynamic Vlookup based on selections

sample.xlsmI am trying to create a sheet with dynamic lookup based on selections.
So if a user selects planning or costing and then the state then click on search then it cell B13 it should lookup from data sheet and give full state name and in cell C13 give the document name and in D13 give the print rule.
If in the data sheet the document type says planning/costing and there is no corresponding state to that document then it should be incliuded in all searches.
When I click clear then it resets the search.

So if i slect Planning and state as AL then the display should be
Alabama ABC DEF
A12 AA1 (aligned to previous row)
C12 AA3 (aligned to previous row)
E12 AA5 (aligned to previous row)

Also is it a better option to do planning and costing as a radio button or drop down list.
Is there a way in the state selections to show the drop down list arrow at all times.
Currently when the cell is not selcted it disappears.

Any help is appreciated!

Hi.

In my opinion, it is better to do the Planning/Costing option as a drop-down list. Using a drop-down list can actually mean you don't need to use VBA to execute the search, but rather Array Formulas.

I have uploaded a workbook with an example of this (called "AJRYAN88").

I hope this helps

P.S. If you would prefer it to be implemented as per your original setup instead, let me know and I will help you out, but I always prefer non-VBA solutions as they are better for the majority of users

Hi ajryan88,
Thank you so much for taking the time to help me out !
I looked at your file and this I think could work but the problem is that there are multiple people going to be touching this file and the chances that the formula breaks is quite high..so even though this works if it is as a code in the sheet It won't break due to user intervention.
I really like the drop down list as Planning/costing as opposed to the radio button.This looks a lot cleaner.
Do you think you can help the VBA way?
I appreciate it.
Thanks!

Sure thing

The VBA implementation is on the "AJRYAN88 VBA" worksheet.

Let me know

This works great, I just need one slight modification.
There should be a "Reset" button so that if the user wants to reset the search they can do so.
Thank you!!

I can implement that if you still want it, however I coded it such that clearing one of the "STATE" or "TYPE" cells will reset the list.

Let me know if you still want a Reset button...

Yeah no worries. If I am guessing correctly, you are saying that the empty states on the DATA worksheet now say "Multi" instead of saying nothing at all? If so, just replace

Please Login or Register  to view this content.

with

Please Login or Register  to view this content.

I hope this helps

You were quick I was just thinking about this and changed my previous post!

Not a problem. Try this

This is perfect !!
Thank you so much !!